将原始 SQL 查询转换为 Laravel Eloquent
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/53079656/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Convert Raw SQL Query to Laravel Eloquent
提问by DIGITAL JEDI
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.
我的项目是一个内容管理系统，它的新版本将集成到 Laravel 框架中；并且有许多巨大的 SQL 查询需要转换为雄辩的语法。
I found the following toolto 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.
谢谢你。
回答by C.V
I found this Online Tool. It can help someone who looking the tool to do the job faster. It may not correct all the time.
我找到了这个在线工具。它可以帮助寻找该工具的人更快地完成工作。它可能不会一直正确。
回答by nakov
Thisis 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();
回答by Shaielndra Gupta
use this
用这个
DB::table('product')
->select('id','name','code','productprice.price','productpictures.image')
->join('productprice','productprice.id' = 'product.id')
->join('image','productpictures.id' = 'product.id')
->where('product.categoryid',1);
->get();
回答by Szabó Kevin
Hy try this way
试试这个方法
Model::select(
'id',
'name',
'code',
DB::raw('( SELECT price FROM productprice WHERE id = p.id ) AS price,'),
DB::raw('( SELECT image FROM productpictures WHERE id = p.id LIMIT 1 ) AS image')
) ->where('categoryid', 1);
This code create a same SQL string by the example. But i think this not the best way!
此代码通过示例创建了相同的 SQL 字符串。但我认为这不是最好的方法！
回答by Kyle Wardle
Really your SQL should be using joins like so :
真的你的 SQL 应该像这样使用连接：
SELECT
p.id,
p.name,
p.code,
pprice.price,
ppictures.image
FROM product p
LEFT JOIN productprice pprice on p.id = pprice.id
LEFT JOIN productpictures ppictures on p.id = ppictures.id
WHERE p.categoryid = 1;
You can read more hereabout left joins.
您可以在此处阅读有关左联接的更多信息。
Also its probably not advisory to match ID's like that, it would be better to have a product_id on the productpictures and productprices and then you can have a foreign key constraint on that to the products table.
此外，它可能不建议像这样匹配 ID，最好在 productpictures 和 productprices 上有一个 product_id，然后你可以在 products 表上有一个外键约束。
But alas that is not the question. To make this into laravel eloquent you should use relationshipsand then you can simply do something like this:
但可惜这不是问题。要使其成为 Laravel eloquent，您应该使用关系，然后您可以简单地执行以下操作：
$Product = Product::select('id', 'name', 'code')
->with('ProductPrice', 'ProductPictures')
->where('categoryid', 1)
->get();
and you will be able to pull that out like so:
你将能够像这样把它拉出来：
$Product->id;
$Product->name;
$Product->code;
$Product->ProductPrice->price;
$Product->ProductPicture->image;
回答by DPS
You should mention table alias name or table name in where clause otherwise it will give integrity violation constraint.
您应该在 where 子句中提及表别名或表名，否则会给出完整性违规约束。
DB::table('product as p')
->join('productprice', 'product.id', '=', 'productprice.id')
->join('productpictures','currencies.id','=','product.id ')
->select('p.id' ,'p.name' , 'p.code' , 'productprice.price',
'productpictures.image as image')
->where('p.categoryid',1)
->get();
回答by Alan Arturo Loya Favela
Update accesos set salida="2019-05-05 12:00:00" where salida = "0000-00-00 00:00:00"
回答by Akshay Zingade
SELECT *
FROM tbl_users as tu
WHERE
tu.rank!='Admin' AND
#where there is space (less than 2 children)
(SELECT COUNT(*)
FROM tbl_users
WHERE under_of=tu.username) < 2
ORDER BY
#distance from top of tree
tu.id,
#by free space
(SELECT COUNT(*)
FROM tbl_users
WHERE under_of=tu.username),
#leftmost
tu.id
Convert SQL query to Laravel query
将 SQL 查询转换为 Laravel 查询
