CODEWITHSUNDEEP
mysqllaravel
Paul Godard
Paul Godard

Reputation: 1131

Query inside Laravel gives different results than from phpMyAdmin

Here is my query :

$posts = DB::table('posts')
    ->join('articles', 'article_id', '=', 'articles.id')
    ->join('users', 'posts.user_id', '=', 'users.id')
    ->select('posts.*', 'articles.title', 'users.name')
    ->orderBy('posts.created_at', 'desc')
    ->get();

The output of dd($users) is:

array:2 [▼
0 => {#369 ▼
+"id": 2
+"created_at": "2021-02-24 14:37:59"
+"updated_at": "2021-02-24 14:37:59"
+"is_active": 1
+"title": "Uthando (Love) SA's COVID focus"
+"body": "ebhbe"
+"article_id": 1
+"user_id": 1
+"name": "Paul Godard"

I have been pulling my hair off to understand why the title of the associated article is not there. So I ran the same query in phpMyAdmin:

SELECT posts.*, articles.title, users.name FROM posts JOIN articles ON posts.article_id = articles.id JOIN users ON posts.user_id = users.id ORDER BY posts.created_at DESC;

... which displays the title of the article correctly!

Then I use the query in Laravel:

$posts = DB::select("SELECT posts.*, articles.title, users.name FROM posts JOIN articles ON posts.article_id = articles.id JOIN users ON posts.user_id = users.id ORDER BY posts.created_at DESC");

... and it is still not working!

Why is going on there?

Upvotes: 1

Views: 304

Answers (1)

Tim Lewis
Tim Lewis

Reputation: 29258

If posts and articles both have a title column, then you need to alias one of them, otherwise the title property of your returned stdClass object won't know which to display.

Try this:

$posts = DB::table('posts')
->join('articles', 'article_id', '=', 'articles.id')
->join('users', 'posts.user_id', '=', 'users.id')
->selectRaw("posts.*, articles.title as `article_title`, users.name as `user_name`")
->orderBy('posts.created_at', 'desc')
->get();

This should return:

...
title: "Post Title",
article_title: "Article Title",
user_name: "User Name",

Edit: Can use select() instead of selectRaw(); wasn't sure, but confirmed. Either or is fine:

...
select('posts.*', 'articles.title as article_title', 'users.name as user_name')
...

Upvotes: 1

Related Questions