You do know that eloquent queries are really a short-cut right? At run time they are converted to regular sql to be processed. So if the above works why not just use it? I use laravel, but mostly I use the query builder. Some one will probably throw in an answer, but I usually prefer regular queries.

jimgwhit said:

You do know that eloquent queries are really a short-cut right? At run time they are converted to regular sql to be processed. So if the above works why not just use it? I use laravel, but mostly I use the query builder. Some one will probably throw in an answer, but I usually prefer regular queries.

Thank you for your observation. The only downside to using regular SQL queries is losing access to convenient Model methods.

I appreciate Eloquent shortcuts and as my knowledge and understanding of it grows I will likely see more clearly when I reached Eloquent limitations. Perhaps any perceived limitations are merely a matter of not knowing "how it's done".

I would though enjoy any snippets, although partial, to illustrate how Eloquent might achieve some of the advanced aspects of my sample SQL query. :)

Hey Larry, you can as I have in my line of work use Eloquent to generate that kind of query ( and many more complex ones as described to a certain degree here )

I would however recommend as @jimgwhit does that you use hand crafted SQL, I would suggest using either the models hydrateRaw or hydrate method to fill it and therefore enable you to continue using the eloquent shortcuts that you are familiar with.

As for formatting your hand crafted SQL into Eloquent's query builder (called Fluent) the below may work, although I haven't tested it (and it was written entirely from memory and within notepad):

$result = DB::table('`contact_place` as `cp`')
	->select(
		DB::raw("CONCAT(`places`.`path`, `child_places`.`id`, '/') as `path`"),
		'`cp`.`place_id`',
		'`places`.`slug` AS `place_slug',
		'`child_places`.`slug` AS `child_place_slug`',
		'`contacts`.`slug` AS `contact_slug`',
		'`logs`.`dateTimeAtNormalized`',
		'`logs`.`logable_type`'
	)
	->join('places', '`places`.`id`', '=', '`cp`.`place_id`')
	->join('`place_place` AS `pp`', '`pp`.`parent_place_id`', '=', '`places`.`id`')
	->join('`places` AS `child_places`', '`child_places`.`id`', '=', '`pp`.`child_place_id`')
	->leftJoin('`contact_place` AS `place_contacts`', '`place_contacts`.`place_id`', '=', '`pp`.`child_place_id`')
	->leftJoin('contacts', '`contacts`.`id`', '=', '`place_contacts`.`contact_id`')
	->rightJoin('logs', '`logs`.`logable_id`', '=', '`pp`.`child_place_id`')
	->where( function( $query ){
		$query->where('`cp`.`contact_id`', 2);
		$query->orWhereRaw('CONCAT(`places`.`path`, `child_places`.`id`, '/') LIKE ?', array('17/18%'))
	})
	->whereRaw('`logs`.`logable_type` REGEXP ?', array('^App.PlaceModel'))
	->get();

You will notice from the above that where there is missing functionality within Eloquent I have used raw query strings, in doing so this will only work on certain dialects of SQL. The reason why Eloquent doesn't usually contain such functionality is largely due to it being a one size fits all and needing to support query generation across a range of SQL dialects and therefore must stick to just those functions that are shared between all those it supports.

For anyone following along, this Laracast provides some valuable insights: https://laracasts.com/series/advanced-eloquent/episodes/5