Multiple Joins and filter with QueryBuilder
Hey Guys,
I am creating a table which has multiple many-to-many relation,
so, I created a query to join all the tables and pass that to $table
And tried to filter columns of table other than contacts using Query Builder, It wont work.
But by using custom filter it works fine
How can I achieve same with Query Builder, hope this gets your attention.
12 Replies
Did you check the resulting SQL query via Debugbar or similar?
yes, but like in custom filter I can directly filter on top of getTableQuery() but in Query builder It ask for the relationship.
If I provide the relationship it work but not on top of getTableQuery() and gives unexpected data.
As you can see I have multiple table and those are with many-to-many relation.
So, when I apply filter for event id and status = "Buyer". It gives contact from that event and contact which are Buyer also in different event.
but like in custom filter I can directly filter on top of getTableQuery() but in Query builder It ask for the relationship.I can't follow. All filters work on the Query builder?
If I provide the relationship it work but not on top of getTableQuery()When does it work then?
when I apply filter for event id and status = "Buyer". It gives contact from that event and contact which are Buyer also in different event.Can you show the resulting SQL query?
I am doing it like this
QueryBuilder::make()
->constraints([
SelectConstraint::make('status')
->options([
'Registered' => 'Registered',
'Checked In' => 'Checked In',
'Buyer' => 'Buyer',
]),
])
which result in this error
SELECT
count(*) AS aggregate
FROM
"contacts"
LEFT JOIN "contact_event" ON "contacts"."mfm_contact_id" = "contact_event"."contact_id"
LEFT JOIN "events" ON "contact_event"."event_id" = "events"."mfm_event_id"
LEFT JOIN "coaching_contact" ON "contacts"."id" = "coaching_contact"."contact_id"
LEFT JOIN "coachings" ON "coaching_contact"."coaching_id" = "coachings"."id"
LEFT JOIN "invoices" ON "invoices"."contact_id" = "contacts"."mfm_contact_id"
LEFT JOIN "services" ON "invoices"."service_id" = "services"."id"
WHERE
("contacts"."status" = Buyer)
AND "contacts"."deleted_at" IS NULL
here it is searching for status in contacts but status is in contact_event how can I specify the table name
And when pass the relationship
SelectConstraint::make('contactEvents.status')
->options([
'Registered' => 'Registered',
'Checked In' => 'Checked In',
'Buyer' => 'Buyer',
]),
this query gets executed
select "contacts"."id", "contacts"."name", "contacts"."state", "contacts"."additionalEmails", "contacts"."additionalPhones", "contacts"."phone", "contacts"."email", "contacts"."created_at", "contacts"."stage", "events"."mfm_event_id", "events"."venue_name", "events"."event_type", "events"."market_group", "services"."service_name", "coachings"."partnership_name", "contact_event"."status" from "contacts" left join "contact_event" on "contacts"."mfm_contact_id" = "contact_event"."contact_id" left join "events" on "contact_event"."event_id" = "events"."mfm_event_id" left join "coaching_contact" on "contacts"."id" = "coaching_contact"."contact_id" left join "coachings" on "coaching_contact"."coaching_id" = "coachings"."id" left join "invoices" on "invoices"."contact_id" = "contacts"."mfm_contact_id" left join "services" on "invoices"."service_id" = "services"."id" where (exists (select * from "contact_event" where "contacts"."mfm_contact_id" = "contact_event"."contact_id" and "contact_event"."status" = ?)) and "contacts"."deleted_at" is null order by "contacts"."created_at" desc, "created_at" desc limit 40 offset 0 {"bindings":["Buyer"],"time":48.57}
it gives multiple status of that contact. bcz he/she has different status in different event
but the custom filter works fine it gives all status as buyer
I thought the custom one is making issues? 🙈
custom filter work fine, but I wanted to make filter select-able like in Query builder
user can select one filter a time, I thought using Query builder would create that easily.
if there is any way to that with custom filter that will also work for me.
I just don't want to show all filters like this.
Can't you just pass multiple custom filters to the query builder?
nope, I tried that by using
QueryBuilder::make()
->filters([]),
but there no filters method in query builder
Ah, I see. Didn't know the query builder uses constraints and not filters.
I guess you could use the
->attribute() method on the constraint to fix the column name?
Maybe try:
nope, it accepts relationship.column and gives same result as before.
It simply checks which contact is Buyer than give all the status of that contact even if other status in not buyer.
It seems like filter is working separately from table. If didn't joined tables than that output would be understandable.