Missing duplicate rows in result of Mjoin

hzhong

Hello, I was trying to perform a mjoin as below. But after checking, I found the result only keeps unique rows. All duplicate rows are not shown. Would someone please have a look and give a hand? Thanks!

// get actual material used for each tank
->join(
Mjoin::inst( 'maconsumption' )
->link( 'vessel_tanks_project.vessel_tank_id', 'daily_inspection.tank_id' )
->where('vessel_tanks_project.project_id', $project_id)
->where('daily_inspection.project_id', $project_id)
->link( 'daily_inspection.di_id', 'maconsumption.di_id' )
->fields(
Field::inst( 'size_id' ),
Field::inst( 'quantity' )
)
)

allan

That Mjoin should be selecting from and updating the junction (link) table daily_inspection with the options that are submitted from the client-side.

Could you show me a sample of the data you are using and the resulting output that is a problem please?

Allan

hzhong

Thank you for the reply Allan!
Please see below sample of 3 table in my Mjoin,
vessel_tanks_project:

daily_inspection:

maconsumption:

The result set of Mjoin as shown below only gave me the distinct combination of fields I want to read. It looks like it performs an additional GROUP BY on the result set.

Old Mjoin:
->join(
Mjoin::inst( 'maconsumption' )
->link( 'vessel_tanks_project.vessel_tank_id', 'daily_inspection.tank_id' )
->where('vessel_tanks_project.project_id', $project_id)
->where('daily_inspection.project_id', $project_id)
->link( 'daily_inspection.di_id', 'maconsumption.di_id' )
->fields(
Field::inst( 'size_id' ),
Field::inst( 'quantity' )
)
)

FYI, I solved this issue by adding the primary key into the fields to read, and it gave me all result set as below

Updated Mjoin which solved this issue:
->join(
Mjoin::inst( 'maconsumption' )
->link( 'vessel_tanks_project.vessel_tank_id', 'daily_inspection.tank_id' )
->where('vessel_tanks_project.project_id', $project_id)
->where('daily_inspection.project_id', $project_id)
->link( 'daily_inspection.di_id', 'maconsumption.di_id' )
->fields(
Field::inst( 'di_id' ),
Field::inst( 'identifier' ),
Field::inst( 'size_id' ),
Field::inst( 'quantity' )
)
)

Could you please advise me why this happened?
Appreciate your time looking at it and helping me!

allan

So the first thing to say is that the junction table used with the Mjoin should be a junction only (i.e. two primary key references) - it should not contain any other data as it will be deleted!

The way the edit works for a junction table with Mjoin is that it will delete all matching rows, and then insert the newly required ones (rather than attempting to do a diff in sql). In this case at least the date and hour information would be lost!

A single Editor doesn't really have a good way to handle that situation at the moment I'm afraid. The approach I'd recommend is to use a child row for editing the Mjoined information (or adding / deleting links).

Allan

hzhong

Thank you for the feedback Allan!

Actually I'm not using Mjoin for updating any links. I'm just using it to get an array of data for display purpose. The editor only edits data in the table of 'vessel_tanks_project'. I think it should be safe in this case, right?

What I really feel confused is as I mentioned above, the result array of Mjoin only gave me the distinct combination of fields. I have to put primary key into fields to read to avoid missing any records because in this way each combination is unique. Do you know why it works like this?

Thank you!

allan

Ah! Yup, if you are doing this as read only, then yes it is safe (make sure you add ->set(false) on the Mjoin instance to make sure no one sends a request to the server which would cause data to be deleted!).

The distinct is being caused by this line. You could just remove that if you don't want it.

If I recall correctly, that line was added for cases where an object was bring returned from the Mjoin rather than an array, so it might not be appropriate to have it in there any more (we don't document the object option now - leftJoin is much better).

Allan

hzhong

Thanks Allan! Clear now.