CODEWITHSUNDEEP
phpmysqllaravel
renathy
renathy

Reputation: 5355

writing Laravel query - count of related model is positive

Unable to write query in Laravel. I am quite new in Laravel and I am not used to write queries using Eloquent model yet.

I have two models: Translation and Category. Each Translation has One Category. Translation has fields lang_from_code and lang_to_code. I have FK defined in DB (those were created automatically during migration).

This took me a while, but at the end I have defined models like this:

class Translation extends Model
{
    public function category() {
        return $this->belongsTo('App\Category', 'category_id');
    }        
}

and

class Category extends Model
{
    public function translations() {
        return $this->hasMany('App\Translation');
    }
}

What I need is to write query like this: "Get one random category from all categories from given list (non-technical) which has at leat one translation from lang1 to lang2".

Query would look like this:

select * from categories c
where c.is_technical = 0 and c.id in (1,2,3,4,5..)
and (select count(*) from translations t where t.category_id = c.id and t.lang_from_code = 'givencode1' and t.lang_to_code = 'givencode2')) > 0

and then random category would be taken.

The best I could write is like this:

$cat = str_split ($cat); //comma seperated category ids
$lang_from = 'lv';
$lang_to = 'en';
$category = Category::where('is_technical', '=', 0)->whereIn('id', $cat)->orderByRaw("RAND()")->first();

However, I do not know how to write "has at least translation from this category" and also " adding languages from and to.

Upvotes: 1

Views: 124

Answers (1)

benJ
benJ

Reputation: 2580

You can use Category::has('translations')

Updated to include translations of a certain type. You'll need to amend as necessary!

Something like this might work:

$category = Category::whereHas('translations', function($query) use ($givencode1, $givencode2) {
                $query->where('lang_from_code', $givencode1)
                      ->where('lang_to_code', $givencode2);
            })
            ->where('is_technical', false)
            ->orderByRaw('RAND()')
            ->first();

Upvotes: 2

Related Questions