How to pass a filter to mjoin?

nicoledramirez@hotmail.com

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", "=")))

allan

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_organization and then use Where() to filter that down. Doing the Where() 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

nicoledramirez@hotmail.com

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.

allan

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_id or 1989? That would need to do in the Options class - however, there is a bit of a "trick" there. The Options will 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