Home Ask Login Register

Developers Planet

Your answer is one click away!

Chriz74 February 2016

get data from table and from relative foreign tables in SQL

Hi have a text search input that looks for matching records in the DB and gets all the data from a table:

let's say like this:

$q = Input::get('items');

        $q = "\"" . "($q)" . "\"";

        $items = DB::table('my_items')->whereRaw(

            'MATCH(item_name) AGAINST(? IN BOOLEAN MODE)',



So I get all the items in the DB from my textsearch, then I send the result as json to some script that updates my page with the items:

return Response()->json($items);

The relations are:


public function brand(){

        return $this->hasOne('App\Brand', 'id', 'brand_id');


public function My_item(){

        return $this->belongsToMany('App\My_item');


Now the problem here is that in 'my_items' table I have some data as IDs that reference foreign tables. For example I will have a 'brand_id' that for example references a 'brands' table where I can have information regarding the brand. So for example I could have brand_id = 3 that means 'Microsoft' in my brands table (id = 3, name = microsoft). Now what I need to do is not only passing the brand_id to my view but also the actual information (name), in this case Microsoft so that I can put that info in the item description. But, how can I get that information before sending with that query? Is there some sort of flag I can use in the query like $items = DB::table bla bla with foreign?


Jilson Thomas February 2016

First of all, you can simplify your search query to something like this:

My_item::where('item_name', 'LIKE', "%$q%")->get();

Now, assign relations the relation to your other tables in your Models. Then you can get all information using the following syntax:

My_item::with('brand')->where('item_name', 'LIKE', "%$q%")->get();

Read more about relations here: https://laravel.com/docs/5.1/eloquent-relationships

Chriz74 February 2016

this way works, DB:: method is dropped for:

$items = My_item::with('brand')->where('item_name', 'LIKE', "%$q%")->get();

this one doesn't:

DB::table('my_items')::with('brand')->where('item_name', 'LIKE', "%$q%")->get();

Post Status

Asked in February 2016
Viewed 1,363 times
Voted 14
Answered 2 times


Leave an answer

Quote of the day: live life