Level 6
Hello . You explained very very briefly. I think the rest of us don't know what you are
Jan 24, 2023
4
Level 3
Need help turning SQL query into Eloquent statement
Here is a sanitized version of the query itself:
SELECT
[...]
FROM
(
(SELECT [...] FROM tableA) as a
INNER JOIN
(SELECT [...] FROM tableB) as b
ON
a.date = b.date
)
LEFT JOIN
sites
ON
a.sites_id = sites.id
Here is the closest I've gotten:
DB::select('[...]')
->fromSub(function ($query) {
$tableA = DB::table('tableA')->select('[...] as a');
$tableB = DB::table('tableB')->select('[...] as b');
$query->table($tableA)
->join($tableB, 'a.date', '=', 'b.date')
->select('tableA.*', 'tableB.*');
})
->join('sites', 'a.sites_id', '=', 'sites.id')
->get();
That doesn't quite do it though. All I've gotten so far is errors, typically Call to undefined method as I trial-and-error my way through this.
Help is very appreciated, thank you! Let me know if there's any more context I can provide that would be helpful.
Level 2
https://jjlabajo.github.io/SQLtoEloquent/
try converting your SQL query to laravel eloquent query.
Level 3
@Akash_kushwaha I have tried that - very cool tool, but it never comes out quite right when I need it for very complex queries like this one. I put in the above query with some real parameters, and got the following:
DB::table("traffic as t")
->join("display as d", function($join){
$join->on("t.date", "=", "d.date");
})
->leftJoin("sites", function($join){
$join->on("t.sites_id", "=", "sites.id");
})
->select("t.date", "impressions", "pageviews")
->get();
...Which actually causes a fatal error on my localhost and forced the server to stop. I messed with it for a while and still couldn't get it to work. I'm pretty stumped here.
Level 75
@Coaster132 if you have a good working query, I suggest using as is, see this post:
https://laracasts.com/discuss/channels/laravel/sql-native-to-query-builder
Please or to participate in this conversation.