CODEWITHSUNDEEP
phpsqllaravelquery-builderhierarchical-data
Tommy Tang
Tommy Tang

Reputation: 239

Laravel select query to get rows where a search value is in one of two columns and include qualifying parent and child rows

Here is my table

Table Food
id    sub(foreign key Food.id)   name
1                                Apple
2      1                         Apple juice
3      2                         Apple juice product
4                                Orange

How can I get my result like:

If I search by id = 1, the result return id 1 ,2 ,3.

If I search by id = 2, the result return id 1 ,2 ,3.

If I search by id = 4, the result return id 4.

Do I need to use a loop or is there another way?

I want to get all related to id = 1 using sub foreign key id(2).sub is 1 and result include id(2), and id(3).sub also related to id(2), so that result include id(3).

Upvotes: 0

Views: 82

Answers (3)

mickmackusa
mickmackusa

Reputation: 48041

If your requirement is to find the search integer:

  1. in the id or sub of a given row
  2. OR in the sub value of a row related by its id value to a qualifying row
  3. OR in the id value of a row related by its sub value to a qualifying row,

then you can LEFT JOIN the food table to itself twice. Then check if the search value is found in any of the qualifying columns.

Code: (PHPize Demo)

DB::table('food')
->select('food.*')
->leftJoin('food AS fup', fn($join) => $join->on('food.sub', '=', 'fup.id'))
->leftJoin('food AS fdown', fn($join) => $join->on('food.id', '=', 'fdown.sub'))
->whereRaw('? IN (food.id, food.sub, fup.sub, fdown.id)')
->setBindings([$find])
->get()
Find Ids in result set rows
1 1, 2, 3
2 1, 2, 3
3 2, 3
4 4

Upvotes: 0

Mehravish Temkar
Mehravish Temkar

Reputation: 4365

This can help you get the related data:

$lists_id = DB::table('food')
        ->where('id', $search_id)
        ->orWhere('sub', $search_id)
        ->lists('id')->toArray();

$sub_id = DB::table('food')
        ->where('id', $search_id)
        ->orWhere('sub', $search_id)
        ->lists('sub')->toArray();

$lists_id = array_merge($lists_id, $sub_id);

$food = DB::table('food')
        ->whereIn('id', $lists_id)
        ->orWhereIn('sub', $lists_id)
        ->get();

Upvotes: 1

Eric Van Joshnon
Eric Van Joshnon

Reputation: 191

I think what you are looking for is Parameter Grouping https://laravel.com/docs/master/queries#parameter-grouping

DB::table('food')
        ->where(function ($query) {
            $query->where('id', 1)
                  ->orWhere('sub', 1);
        })
        ->get();

Upvotes: 1

Related Questions