1

I have the following

return DB::table('recipe_symbols')
   ->where('symbol_id', 8)
   ->get(['recipe_id']);

it works just fine. Now I have the following to return a more filtered result

return DB::table('recipe_symbols')
   ->where('symbol_id', 8)
   ->where('symbol_id', 16)
   ->get(['recipe_id']);

I get no results, even though the rows with symbol_id 8 and 16 exists. I tried with raw query and still the same issue.

What is going wrong? What I want to achieve is get recipes by symbol_id based on what symbols the user is selecting.

I tried whereIn() but that brings back recipes that have a certain symbol_id but don't have the other. For example it brings 2 recipes, from which one has a symbol_id of 8 only and second has both 8 and 16. I need to get all the recipes that have symbol_id = 8 and 16, nothing else.

EDIT*** Database structure

recipe_symbols_table
----------------------
|id | recipe_id | symbol_id |
-----------------------------
|1  | 2         | 8
|2  | 2         | 16
|3  | 3         | 8
|4  | 3         | 16
|5  | 4         | 8
|6  | 4         | 30
|7  | 5         | 8
|8  | 5         | 28    
|9  | 6         | 8
|10 | 6         | 31
|11 | 7         | 8
|12 | 7         | 18

EDIT***

$sql = 'SELECT * FROM sie_hp_cookbook_recipes_symbols WHERE symbol_id=8 and symbol_id=16';
$qry = DB::select($sql);
Improve this question
3
  • I need to return only the first 2 results Commented Jul 26, 2015 at 15:42
  • Could you add the raw query you ran to the question? Commented Jul 26, 2015 at 15:44
  • @cjds I edited the question Commented Jul 26, 2015 at 16:01

3 Answers 3

Reset to default
0

Following gives me the first two results (2 and 3). Hope this helps!

SELECT a.`recipe_id`
FROM `recipe_symbols_table` a
    INNER JOIN `recipe_symbols_table` b
        ON a.`recipe_id` = b.`recipe_id`
WHERE b.`symbol_id` IN (8,16)
GROUP BY a.`recipe_id`
HAVING COUNT(DISTINCT b.`symbol_id`) = 2
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

If I understand correctly you want all recipe_id where certain symbols are present.

This is hard in MySQL

This is not going to be trivial in MySQL. Because MySQL parses row by row just doing an AND will return nothing. One row can't have a symbol_id of 8 and 16.

So we need to compare two tables with the same rows. This is usually done with a JOIN and an intersect. But MySQL doesn't support intersect. (Hence problems.)

How to solve it

Using a combination of UNION and GROUP BY we can simulate an interssection but I have no idea how to do it with Eloquent.

Thus your best bet is a raw_query. The raw MySQL query for this will be

SELECT recipe_id from (
 (SELECT * FROM sie_hp_cookbook_recipes_symbols WHERE symbol_id=8)
 UNION ALL 
(SELECT * FROM sie_hp_cookbook_recipes_symbols WHERE symbol_id=16)
) AS t1 GROUP BY recipe_id HAVING count(*) >= 2;

Link to an example fiddle http://sqlfiddle.com/#!9/c4150/15/0

Now a Laravel solution

$first= DB::table('recipe_symbols')
                           ->where('symbol_id', 8);
return DB::table('recipe_symbols')->where('symbol_id', 16)
                           ->unionAll($first)
                           ->groupBy('recipe_id')
                           ->having('count(*)>2')
                           ->select(['recipe_id'])
                           ->get();

Complicated, eh!

Improve this answer

3 Comments

Your solution is working, not completely but is working. On top of that simple query I have to join the recipes table which I did but on some different symbol_id selection is bringing some results that it shouldn't, I guess that's for another topic. Should I accept? (in my opinion it makes clear why my first approach wouldn't work, so I believe yes?)
You could accept and post another question if you feel it is sufficiently different. Why don't you come in on chat and we'll try to solve the other problem if it is different
the accepted answer is the right solution, without union, it works perfectly and thank you
0

raw query

SELECT a.recipe_id FROM (
  (select * from recype_symbols where symbol_id = 8) a 
  join (select * from recype_symbols where symbol_id = 16) b 
  on 
  a.recipe_id = b.recipe_id)

I'm not sure if this is correctly in laravel way

 DB::table('recype_symbols')
            ->join(DB::raw('(select *  from recype_symbols where symbol_id = 16 or symbol_id = 8) as b'),'recype_symbols.recipe_id','=','b.recipe_id')
                ->where('recype_symbols.symbol_id',8)
                ->where('b.symbol_id',16)
                ->select(['recype_symbols.recipe_id'])
                ->get();
Improve this answer

7 Comments

same results like whereIn(), it return the recipes that have a symbol_id 8 but not 16 and the ones that meet the where clause
I've seen your edit, I get the same results, it's driving me nuts
it does, it's insane can't figure it out why
If you query db DB::table('recipe_symbols')->where('symbol_id', 16)->select(['recipe_id'])->get(); Is result 16
yes it does, I just double checked everything. I have 5 recipes, 2 of them whom have a symbol_id = 8, 16, 27. I have another 3 that have symbol_id = 8, 30. I need to return the first 2. If I use whereIn() or orWhere() I get all of them because they all have symbol_id = 8
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.