Laravel join with LIKE

posted 10 years ago

Hi there!

I have got two SQL issues:

I need to join two tables by comparing the field tx_ard_users.uid to either tx_ard_researches.incharge or tx_ard_researches.collaborators. I'm not sure my function is correct... can you help me?
The fields tx_ard_researches.incharge and tx_ard_researches.collaborators are BLOB fields in mySQL. Using LIKE is the best thing I found, but it's not correct. Do you have any better idea?


$researches = DB::table('tx_ard_researches')
	->join('tx_ard_users', function($join) {
		$join->on('tx_ard_researches.incharge', 'LIKE', "%"."tx_ard_users.uid"."%")
		->orOn('tx_ard_researches.collaborators', 'LIKE', "%"."tx_ard_users.uid"."%");
	})

        ->get();

Thank you guys for your help!

Last updated 2 years ago.

ayoze

replied 10 years ago

Why are you concatenating: "%"."tx_ard_users.uid"."%" ?

Last updated 2 years ago.

catherine111

replied 10 years ago

Hmmm... I thought I had to do it this was to have the value between %% as it is a LIKE... do you have a better idea? I'd be glad! :-D

Last updated 2 years ago.

ayoze

replied 10 years ago

Just do "%tx_ard_users.uid%" :)

Last updated 2 years ago.

jarektkaczyk

replied 10 years ago

I think that in fact you need to concatenate

...
$join->on('tx_ard_researches.incharge', 'LIKE', DB::raw( "CONCAT('%', tx_ard_users.uid, '%')
" ))...

Not sure of % and performance of this query though, depends on your needs.

Last updated 2 years ago.

ayoze

replied 10 years ago

Yes, this is right, im sorry

Last updated 2 years ago.

catherine111

replied 10 years ago

Thank you guys! You're great!

Last updated 2 years ago.

jguix

replied 9 years ago

Great, thanks!

Moderators