How can I write this query using the laravel query builder?


I'm on laravel 5.1 using postgres as the DB. I have a fiddle here in case it helps understand my issue: <a href="https://www.db-fiddle.com/f/5ELU6xinJrXiQJ6u6VH5/4" rel="nofollow">https://www.db-fiddle.com/f/5ELU6xinJrXiQJ6u6VH5/4</a>

with properties as ( select properties.*, json_agg(property_fields.*) as property_fields from properties left join fields as property_fields on property_fields.parent = 'property' and property_fields.parent_id = properties.id group by properties.id, properties.deal_id, properties.address ) select deals.*, json_agg(properties.*) as deal_properties, json_agg(deal_fields.*) as deal_fields from deals left join properties on deals.id = properties.deal_id left join fields deal_fields on deal_fields.parent = 'deal' and deal_fields.parent_id = deals.id group by deals.id, deals.name order by deals.id

Writing most of this is fairly straight forward. The problem I'm having is with the with properties as (...) block. I've tried something like:

DB::statement('WITH properties AS ( ... )') ->table('deals') ->select(' deals.*, json_agg(properties.*) as deal_properties, ') ... ->get();

But I notice the execution stop after DB::statement()

Is there a method in the Query Builder that I'm missing? How can I prefix my query with the WITH properties AS (...) statement?

I think it should also be noted that I'm trying to implement a Repository Pattern and I can't just wrap a DB::statement() around the whole query.


if you want some data fetch from a table you can use this type of code

$user = DB::table('table name')->where('name', 'John')->where('height','!>',"7")->select('table fields which you want to fetch')->get();

Or try using the larevel Eloquent ORM which will make things easier with the database.

for more example or reference

<a href="https://laravel.com/docs/5.0/queries" rel="nofollow">https://laravel.com/docs/5.0/queries</a>


I think you can actually do this with eager loading, assuming that the relationships are set up correctly. (More reading here: <a href="https://laravel.com/docs/5.4/eloquent-relationships#constraining-eager-loads" rel="nofollow">https://laravel.com/docs/5.4/eloquent-relationships#constraining-eager-loads</a>)

So I think you'd be able to add something like

->with(['properties' => function ($query) { $query->select('field') ->leftJoin('join statement') ->groupBy('field1', 'field2'); }])


  • How to load Q library with Require.js?
  • Many to Many in Linq using Dapper
  • How can I prevent the need to copy strings passed to a avr-gcc C++ constructor?
  • SonarQube: Cannot deactivate rule with missing quality profile
  • Question about instantiating object
  • Validate child input components on submit with Vee-Validate and vue js 2
  • Combining two different ActiveRecord collections into one
  • Trouble saving JSON data for use by multiple UIViews [duplicate]
  • saving file generated by TCPDF
  • Force show.bind execution
  • wxPython: displaying multiple widgets in same frame
  • What does 'Language neutral' mean with regard to MAKELANGID?
  • how to save the state in userdefaults of accessory checkmark-iphone
  • Chart.js Multiple dataset
  • Groovy: Unexpected token “:”
  • Android - Material Design - NavigationView - How to put vertical scroll?
  • How to write order and limit within cakephp joins array
  • Custom validator control occupying space even though display set to dynamic
  • Word Open XML Mail Merge
  • Android fill_parent issue
  • FileReader+canvas image loading problem
  • Pass value from viewmodel to script in zk
  • Using $this when not in object context
  • Sails.js/waterline: Executing waterline queries in toJSON function of a model?
  • Deselecting radio buttons while keeping the View Model in synch
  • Getting last autonumber in access
  • Read text file and split every line in MSBuild
  • Counter field in MS Access, how to generate?
  • How to add a column to a Pandas dataframe made of arrays of the n-preceding values of another column
  • Incrementing object id automatically JS constructor (static method and variable)
  • How to check if every primary key value is being referenced as foreign key in another table
  • Can I have the cursor start on a particular column by default in jqgrid's edit mode?
  • jquery mobile loadPage not working
  • Google cloud sdk not working when python points python3
  • How to delete a row from a dynamic generate table using jquery?
  • Unit Testing MVC Web Application in Visual Studio and Problem with QTAgent
  • json Serialization in asp
  • Rails 2: use form_for to build a form covering multiple objects of the same class
  • embed rChart in Markdown
  • How to stop GridView from loading again when I press back button?