Laravel - Using joins with where across two tables

Question

I have table, 'jobs', indexed by job_id, and and table discussions with foreign key 'job_id'.

In jobs I have a column called 'summary', and in discussions a column called 'body'. I want to search for the same text string in both of these columns across both these tables and return the applicable jobs.

Eg, if a jobs record with job_id=7 has the string 'somestring' in it's 'summary' column, and a discussions record has foreign key job_id=20 and has string 'somestring', I want the job records for job_id=7 and job_id=20 returned.

I am using laravel. I'm having trouble finding a solution, this is the closest I have got:

$query->join('discussions', function( $join ) use ( $search_term ){
                $join->on('jobs.job_id', '=', 'discussions.job_id');
                $join->where('discussions.body', 'LIKE', $search_term );                    
                $join->orWhere('jobs.summary', 'LIKE', $search_term );                                  
            })

But this just returns everything from my discussions table. Does anyone have any suggestions?

Thanks :)

Jarek Tkaczyk · Accepted Answer · 2014-06-18 15:58:39Z

1

There's SQL problem with that. What you are doing is basically this:

SELECT * FROM jobs INNER JOIN discussions ON (...) jobs.summary LIKE search_term

There is no WHERE clause, and that part (...) doesn't matter (simplified a bit) since there is OR, so in fact you don't (not only) join the table on the key/fk pair.

You simply need to move those wheres out of the join closure and it will work as expected:

$query->join('discussions', function( $join ) {
     $join->on('jobs.job_id', '=', 'discussions.job_id');
})
    ->where('discussions.body', 'LIKE', $search_term )
    ->orWhere('jobs.summary', 'LIKE', $search_term )
    ->get();

Now your query will look like it should:

SELECT * FROM jobs INNER JOIN discussions ON jobs.id = discussions.job_id 
    WHERE discussions.body LIKE search_term
    OR jobs.summary LIKE search_term

answered Jun 18, 2014 at 15:58

Jarek Tkaczyk

81.4k27 gold badges167 silver badges162 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Ste77 Over a year ago

Thanks for the explanation. I followed what you have put above, and although the query does render as you say, I still am only getting back the results from my discussions table. The jobs that have the text in the jobs.summary are being ignored.

Jarek Tkaczyk Over a year ago

The query is OK for sure, but maybe you need leftJoin instead of (inner) join then?

Ste77 Over a year ago

Cheers, the leftJoin has done the trick in that I am getting back the correct result now :) Thank you very much. However it job_id field is now empty in the results that have come back from the jobs table. Any idea why that could be happening?

Jarek Tkaczyk Over a year ago

Do you know the difference between inner join and left join? You will get all the matching results from the 'left' table (also those that don't have related job, thus job_id might be empty for some of them).

Ste77 Over a year ago

Not really, my MySQL is weak. Will look it up in detail. Thanks for taking the time to go through this. Will mark as accepted answer as although I haven't got exactly what I want, I understand what I have got and why :)

clod986 · Accepted Answer · 2014-06-18 12:44:59Z

0

Try this and see what comes out of it ;)

$query->join('discussions', function( $join ) use ( $search_term ){
                $join->on('jobs.job_id', '=', 'discussions.job_id')
                 ->where('discussions.body', 'LIKE', $search_term )                    
                 ->orWhere('jobs.summary', 'LIKE', $search_term );                               
            })
      ->select('*')
      ->get();

EDIT: I would replace the $query with the correct DB::table('mytable').

DB::table('jobs')->join('discussions', function( $join ) use ( $search_term ){
                $join->on('jobs.job_id', '=', 'discussions.job_id')
                 ->where('discussions.body', 'LIKE', $search_term )                    
                 ->orWhere('jobs.summary', 'LIKE', $search_term );                               
            })
      ->select('*')
      ->get();

EDIT2: I missed a semicolumn, corrected

answered Jun 18, 2014 at 12:44

clod986

2,6677 gold badges32 silver badges52 bronze badges

1 Comment

clod986 Over a year ago

then you have to be more specific with the select clause @Ste77

Collectives™ on Stack Overflow

Laravel - Using joins with where across two tables

2 Answers 2

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related