将原始SQL查询转换为Laravel Eloquent

Question

My project is a content management system & a new version of it will be integrated in a Laravel framework; and there are lots of huge SQL queries which needs to be converted into eloquent syntax.

I found the following tool to convert my already written queries into eloquent syntax, but it does not work.

Could anyone guide me to convert my queries or help me find a tool that could do the job?

Please find below a sample of my queries (note that most of my queries look similar):

SELECT id, name, code,
       ( SELECT price FROM productprice WHERE id = p.id ) AS price,
       ( SELECT image FROM productpictures WHERE id = p.id LIMIT 1 ) 
AS image
FROM product p 
WHERE categoryid = 1

Thank you.

Answer 1

This is the best tool in my opinion :) reading and knowing the documentation, because no tool will be able to do it automagically for every case.

So here is my attempt on your query above:

DB::table('product as p')
   ->select([
       'id',
       'name',
       'code',
       'pp.price',
       'ppic.image'
   ])
   ->join('productprice as pp', 'pp.id', '=', 'p.id')
   ->join('productpictures as ppic', 'ppic.id', '=', 'p.id')
   ->where('categoryid', 1)
   ->get();