How to pass a filter to mjoin?
How to pass a filter to mjoin?
nicoledramirez@hotmail.com
Posts: 60Questions: 14Answers: 2
Hi,
I am trying to pass 1989 to an mjoin. The results are coming back with everything in the Role table, but I only want the results where contact_organization.contact_id is 1989. What is the correct syntax?
.MJoin(new MJoin("Role")
.Link("Contact.id", "contact_organization.contact_id")
.Link("Role.id", "contact_organization.role_id")
.Where("contact_Organization.contact_id", "1989", "=")
.Model<JoinAccess2Model>()
.Order("Role.position")
.Field(new Field("id")
.Options("Role", "id", "position", q => q.Where("active", "1", "=")))
This discussion has been closed.
Answers
Interesting one - I'm not sure I've come across this request before. Normally a query like that would be applied at the top level - i.e. to a left join to
contact_organizationand then useWhere()to filter that down. Doing theWhere()on the Mjoin will mean that you will get all of the Contacts for all contact_ids, but the Mjoined information would be restricted to only contact_id 1989. Is that what you want?Allan
Yes, that is what I want. I am trying to create a select box on the roles returned from the mjoin. I am currently working on the process you described because I got tired of fighting with myself because in my head, I thought it should be able to work. But it isn't. I even tried moving the .where statement elsewhere but it doesn't seem to be limiting anything. I still get all roles out there.
Do you mean in the table display, or in the options list?
Do you want the list of options in the options list to be limited to just those with a linked
contact_idor 1989? That would need to do in theOptionsclass - however, there is a bit of a "trick" there. TheOptionswill do its own query to get the list of options available, so it doesn't automatically include any join information. In fact the Options class doesn't have the option of doing a left join at the moment. If you want to limit the options in the list you'd need to query the database directly in a custom function.Have I understood correctly? If so, let me know and I can show you a simple little function that will do that.
Allan