Raw SQL query to eloquent

Hello laravel users!

So i'm trying to fix a tool that matches the SQL search and return a match by percentage. I've tried and used this SQL to achieve what I want.

SELECT
(company)
, MATCH(company) AGAINST('SEARCH STRING') as relevance
, b.maxrelevance
, (MATCH(company) AGAINST('DATABASE ENTRY TO BE SEARCHED'))/b.maxrelevance*100 as relevanceperc
FROM
watchlists a
, (SELECT MAX(MATCH(company) AGAINST('DATABASE ENTRY TO BE SEARCHED')) as maxrelevance FROM watchlists LIMIT 1) b
WHERE
MATCH(company) AGAINST('DATABASE ENTRY TO BE SEARCHED')
ORDER BY
relevance DESC;

However, I'm trying to convert the above SQL query to eloquent without any success so far. Any help or guidance are more than welcome!

Best regards, Fredrik

jlrdw

4 years ago

Level 75

Look at examples in query builder chapter, and you will need some "raw" expressions.

Eloquent has all query builder methods as well. Not your data but example of how an eloquent query flows:

$quy = Powner::query()->leftJoin('dc_pets', 'dc_powners.ownerid', '=', 'dc_pets.ownerid')
                ->select('dc_powners.ownerid', 'dc_powners.oname')
                ->selectRaw('count(dc_pets.petid) as countOfPets')
                ->groupby('dc_powners.ownerid')
                ->orderby('dc_powners.oname')
                ->get();

Results basically give:

ownerid, oname, countOfPets

Like:

5|Bob|3
4|Greg|9
2|Rob|1

Just build it up like a regular query would need building up. Study the examples in documentation.

2 likes

libellux

4 years ago

Level 1

@jlrdw thanks will have a look. I'll bother you if I need any further assistance :D

Please or to participate in this conversation.