Query builder join table to column like a eloquent

How can we repeat eloquent query using Query builder?

If we need more information from another table on author column

like this

{
 "20": {
    "id": 20,
    "title": "Quia eaque veritatis dolores eum nisi minus.",
    "description": "Sit molestiae fugiat iste magni ab alias sunt.",
    ...
    "author": {
      "username": "smcglynn",
      "email": "smcglynn@"
    }
  },..
}

eloquent

$category->posts()->with('author')

What is the correct way to do this through the query builder?

$tblPost = Post::getModel()
            ->getTable();

$tblUser = User::getModel()
	->getTable();

$builder = DB::table($tblPost)
	->join(
		$tblUser,
		$tblPost.'.user_id',
		'=',
		$tblUser.'.id'
	)->select([
		$tblPost.'.id',
		$tblPost.'.title',
		$tblPost.'.description',

		$tblUser.'.id',
		$tblUser.'.username',
		$tblUser.'.email',
		$tblUser.'.avatar',
	])
	->where('category_id', $category['id'])
	->where('deleted_at', null)
	->get()
	->keyBy('id')
	->toArray();

jlrdw

4 years ago

Level 75

Eloquent can use query builder methods. However there are eloquent relation queries and eloquent queries.

A relation query is always at least two queries. Like a one to many, in the background two queries are performed.

a query to get parent
a query to get child (related) records

So in query builder you can write two queries.

But just curious, why not set up relations.

Edit: Query builder doesn't have a ->toArray(); You can map, see https://laracasts.com/discuss/channels/guides/to-array

And an example (not your data) of an eloquent query using query builder techniques:

$quy = Powner::query()->leftJoin('dc_pets', 'dc_powners.ownerid', '=', 'dc_pets.ownerid')
                ->select('dc_powners.ownerid', 'dc_powners.oname')
                ->selectRaw('count(dc_pets.petid) as countOfPets')
                ->groupby('dc_powners.ownerid')
                ->orderby('dc_powners.oname')
                ->get();

Results basically give:

ownerid, oname, countOfPets

Like:

5|Bob|3
4|Greg|9
2|Rob|1

Just test data and getting the count of a owners pets: In example:

Greg has 9 pets in the related table.

1 like

Speculant

4 years ago

Level 1

@jlrdw I didn't quite understand how your example will allow you to do the same as in json above

jlrdw

4 years ago

Level 75

What is

{
 "20": {
    "id": 20,
    "title": "Quia eaque veritatis dolores eum nisi minus.",
    "description": "Sit molestiae fugiat iste magni ab alias sunt.",
    ...
    "author": {
      "username": "smcglynn",
      "email": "smcglynn@"
    }
  },..
}

Is it a column or query results.

1 like

Speculant

4 years ago

Level 1

@jlrdw its result this eloquent

$category->posts()->with('author')

I want to understand how to do the same using Query builder

jlrdw

4 years ago

Best Answer

Level 75

You could just write 2 queries, but you can try with a subquery.

1 like

Please or to participate in this conversation.