How do you create complex database filters using eloquent?

I have following 2 tables Table 1: creatives

| creative_id | creative_name|
|----------------- | ----------------------|
|        1            |       xyz              |
|        2           |       abc             |
|        3           |       pqr              |

Table 2: term_relationships

| creative_id | term_id         |
|----------------- | -------------------|
|        1            |       1              |
|        1            |       2             |
|        1            |       3             |  
|        2           |       1              |
|        2           |       2             |
|        2           |       4             |  
|        3            |       1             |
|        3            |       2            |
|        3            |       3            |

I want to join the above 2 tables under certain conditions. Here are some scenarios:

Show me creatives for which term_id =1 exists (relatively easy to do)
Show me creatives for which term_id = 1 AND term_id =2 exists (output should be xyz, abc and pqr)
Show me creatives for which term_id = 1 AND term_id = 2 AND term_id =3 exists (output should be xyz and pqr)

I am not sure how to do this in eloquent. Is it doable or should I resort to query builder ?

BTW, the term_id variables are fetched via a form and some of them could be null values.

bobbybouwmann

10 years ago

Level 88

I believe this will do the trick for you

$data = DB::table('creatives')
    ->join('term_relationships', 'creatives.creative_id', '=', 'term_relationships.creative_id')
    ->where('term_id', '=', 1)
    ->orWhere(function($query) {
        $query->where('term_id', '=', 1)
            ->where('term_id', '=' , 2);
    })->orWhere(function($query) {
        $query->where('term_id', '=', 1)
            ->where('term_id', '=' , 2)
            ->where('term_id', '=' , 3);
    })->get();

hvs

10 years ago

Level 1

@bobbybouwmann . Your solution will not work. The correct Mysql query will be

For Scenario 1

SELECT *
FROM   creatives
WHERE  creative_id IN (SELECT   creative_id
                       FROM     term_relationships
                       WHERE    term_id IN (1)
                       GROUP BY creative_id
                       HAVING   COUNT(*) = 1)

For Scenario 2

SELECT *
FROM   creatives
WHERE  creative_id IN (SELECT   creative_id
                       FROM     term_relationships
                       WHERE    term_id IN (1, 2)
                       GROUP BY creative_id
                       HAVING   COUNT(*) = 2)

For Scenario 3

SELECT *
FROM   creatives
WHERE  creative_id IN (SELECT   creative_id
                       FROM     term_relationships
                       WHERE    term_id IN (1, 2, 3)
                       GROUP BY creative_id
                       HAVING   COUNT(*) = 3)

I don't have a prior knowledge of how many filters will be selected by the user.

So how do you build this query in Laravel? I can use Raw query but really don't want to if it can be done more elegantly. Thanks

Please or to participate in this conversation.