3

I do currently have this code:

        return Datatable::query($query = DB::table('acquisitions')
            ->where('acquisitions.deleted_at', '=', null)
            ->where('acquisitions.status', '!=', 2)
            ->join('contacts', 'acquisitions.contact_id', '=', 'contacts.id')
            ->join('user', 'acquisitions.user_id', '=', 'user.id')
            ->select('contacts.*', 'acquisitions.*', 'acquisitions.id as acquisitions_id', 'user.first_name as supervisor_first_name', 'user.last_name as supervisor_last_name', 'user.id as user_id'))

The data from the user table is used for 2 columns: acquisitions.supervisor_id and acquisitions.user_id. I need the first_name and the last_name for both of this tables, the above query does however currently only use the id from the acquisitions.user_id field. I also tried to use a table alias, that does also not work, I assume that I'm doing something wrong here.

So in short: I also need that the query selects the data for the user, based on the id from the acquisitions.supervisor_id and makes it available as supervisor_first_name and supervisor_last_name.

Improve this question
2
  • Have you tried using DB::raw() within the select statement. Commented Mar 11, 2016 at 16:31
  • No, I'm a bit clueless how ti implement it into the query, would appreciate if you could help me out with that. Nortmally I'd just do it with eloquent, unfortunately that is not possible here due to a plugin. Thanks! Commented Mar 11, 2016 at 16:34

3 Answers 3

Reset to default
1

According to your next to last comment on the other answer, you need a self join per reference table. Try this:

$result = DB::select('SELECT
u.name user_first_name,
u.last_name user_last_name,
u.email user_email,
s.name supervisor_name,
s.last_name supervisor_last_name,
s.email supervisor_email
FROM acquisitions a
JOIN users u ON a.user_id = u.id
JOIN users s ON a.supervisor_id = s.id');

return $result;

Note that $result is an array of StdClass objects, not a Collection, but you can still iterate it and call the current item's values:

foreach ($result as $item) {
    print($item->supervisor_first_name);
}

If you need a WHERE clause, e.g. to get a specific user's row from acquisitions, you would do that by adding a parameter to the query like so:

$result = DB::select('SELECT
u.name user_first_name,
u.last_name user_last_name,
u.email user_email,
s.name supervisor_name,
s.last_name supervisor_last_name,
s.email supervisor_email
FROM acquisitions a
JOIN users u ON a.user_id = u.id
JOIN users s ON a.supervisor_id = s.id
WHERE a.user_id = ?
', [3]);

EDIT

If you need the resultset to be a Collection, you can easily convert the array to one, using the hydrate method:

$userdata = \App\User::hydrate($result); // $userdata is now a collection of models
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Thanks a lot, sadly I do now have the issue that the package won't work with it as it appears to be requiring a collection __clone method called on non-object
Make sure you're actually getting valid results, and please see my edit
The query actually works, but does not return exactly what I need. Now I only get the data I specified below (in the comment) but is missing all the other values from the acquisitions table. Regarding the converting to a collection, does that still work if the data is not from a specific table? I never used that function, I mean in this case the datasets should actually be from the acquisitions table and just include the data that the current query (that you created above) is outputting, I hope you know what I mean, I'm quite tired already. Thanks a lot for your time!
TBH I haven't used hydrate much, but since it's a method of the Eloquent model I would imagine it "prefers" to have data from a single table and possibly its relationships as well. In any case, you have to test which works better for you. As for the "other" data you need from acquisitions, just append it in the SELECT statement.
How can you do this is Eloquent?
0

It should be something like;

return Datatable::query($query = DB::table('acquisitions')
    ->where('acquisitions.deleted_at', '=', null)
    ->where('acquisitions.status', '!=', 2)
    ->join('contacts', 'acquisitions.contact_id', '=', 'contacts.id')
    ->join('user', 'acquisitions.user_id', '=', 'user.id')
            ->select( \DB::raw("  contacts.*, acquisitions.*, acquisitions.id as acquisitions_id, user.first_name as supervisor_first_name, user.last_name as supervisor_last_name, user.id as user_id   ") )
);
Improve this answer

4 Comments

Thanks, but that does not help me. I know how to use a Raw query inside the select, I however still don't know how the proper query would have to look to assign the values correctly :/
Besides the fact that I'm quite sure that I would need to adjust the join first.
You cannot use aliases in the query, if that is what you are trying to achieve. What you can do is do a sub query, and use the output in the where statement. Can you provide more details on the issue, as I do not quite understand the exact problem.
I have 2 tables. user and acquisitions. The table acquisitionscontains 2 columns: user_id and supervisor_id. user contains first_name and last_name. Now I want to select the first and last name for both the supervisor_id and the user_id as supervisor_first_name, supervisor_last_name, user_first_name, user_first_name
0 
$devices = DB::table('devices as d')
->leftJoin('users as au', 'd.assigned_user_id', '=', 'au.id')
->leftJoin('users as cu', 'd.completed_by_user_id', '=', 'cu.id')
->select('d.id','au.name as assigned_user_name','cu.name as completed_by_user_name');

Also follow this link

https://github.com/yajra/laravel-datatables/issues/161

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.