Conditions MJoin

belu

I am trying to use a condition on the Joined table with MJoin - I do not want to show archived entries - but it does not work. What do I do wrong?

Thanks in advance!

// Build our Editor instance and process the data coming from _POST
$editor = Editor::inst($db, 'as_users', 'user_id');

########
# Joins
// für die as_user_details
$editor->leftJoin( 'as_user_details as details', 'as_users.user_id', '=', 'details.user_id' );

// as_user_roles
$editor->leftJoin('as_user_roles as roles', 'as_users.user_role', '=', 'roles.role_id');

// Join für den Namen des Users, der dein Eintrag erzeugt hat
$editor->leftJoin('as_user_details as created_details', 'as_users.created_by_user_id', '=', 'created_details.user_id');

// Join für die Qualifikationen 1:n

$editor->join(
    Mjoin::inst('qualifications_names')
        //->validator('qualifications_names[].id', Validate::mjoinMinCount(1))
        ->link('as_users.user_id', 'link_as_users__qualifications_names.user_id')
        ->link('qualifications_names.id', 'link_as_users__qualifications_names.qualification_names_id')
        // TODO archivierte nicht anzeigen
        //->order('name asc')
        ->fields(
            Field::inst('id')
                ->set(Field::SET_NONE)
                ->validator(Validate::required())
                ->options(Options::inst()
                    ->table('qualifications_names')
                    ->value('id')
                    ->label('qualification_name'),
                Field::inst('qualification_name')
                )
        )
        ->where("qualifications_names.archived", "1", "!=")
);

allan

That actually looks correct to me. You shouldn't need the qualifications_names. part in the where statement there, but aside from that (which shouldn't make any difference) it looks okay.

Could you add:

$editor->debug(true);

(before wherever you call the process() method) and then use the debugger to upload a trace for me when you load your page. Hopefully that will let me see what is going wrong. I'll need the 6 character debug code it gives you.

Thanks,
Allan

belu

Hi Alan,

I have uploaded the trace with the debugger... my Code is 'ukunih'.
The thing is, that DataTables or editor do not throw an error, but shows all entries of the Join table and not - like in the where condition only the not archived ones.
I have also tried to delete the table name in the where - condition (as you said), but it does not work.

Thanks a lot in advance!

allan

So the query being run is:

SELECT DISTINCT
   `as_users`.`user_id` as 'dteditor_pkey',
  `qualifications_names`.`id` as 'id'
FROM  as_users as as_users 
JOIN `link_as_users__qualifications_names`
  ON `as_users`.`user_id` = `link_as_users__qualifications_names`.`user_id`   
JOIN `qualifications_names` 
  ON `qualifications_names`.`id` = `link_as_users__qualifications_names`.`qualification_names_id`
WHERE `archived` != :where_0 
...

where the binding :where_0 is 1. So it looks like the query is running successfully.

I wonder if there might be a misunderstanding on my part of what you are trying to do. The Mjoin where will not limit the rows selected from the parent table. It will only limit the rows selected from the Mjoined table. Is that want you want?

Also if you want the where condition to apply to the Options (i.e. the list of options shown for the join), you'd need to add that to the Options instance as well. Possibly that is what is missing here?

Allan

belu

Yes, I would like the condition to be applied to the Options - I tried to insert the condition there, but now none of the options is displayed... Any idea? Thx Allan!

$editor->join(
    Mjoin::inst('qualifications_names')
        ->validator('qualifications_names[].id', Validate::mjoinMinCount(1))
        ->link('as_users.user_id', 'link_as_users__qualifications_names.user_id')
        ->link('qualifications_names.id', 'link_as_users__qualifications_names.qualification_names_id')
        ->order('qualifications_names.priority asc')
        ->fields(
            Field::inst('id')
                ->set(Field::SET_NONE)
                ->validator(Validate::required())
                ->options(Options::inst()
                    ->table('qualifications_names')
                    ->value('id')
                    ->where("qualifications_names.archived", "1", "!=") 
                ->label('qualification_name'),
                Field::inst('qualification_name')
                )
        )
        ->where("archived", 1, "!=")
);

allan

Odd - that looks sensible to me!

Could you add ->debug(true) just before the ->process(...) can and then use the debugger to give me a trace please - click the Upload button and then let me know what the debug code is.

Thanks,
Allan

belu

The Code is: enibex

allan

What's really odd there is that the debug trace is only showing one SQL query, which is on the clients table and has three left joins, which doesn't match the code above (uses table as_users for the host table).

Could you show me your full PHP code for the controller please?

Thanks,
Allan

alaaa95

I have uploaded the trace with the debugger... my Code is 'ukunih'.
The thing is, that DataTables or editor do not throw an error, [url=https://speedtest.vet/]speed test[/url] [url=https://vidmate.bid/]vidmate[/url] [url=https://123-movies.red/]123movies[/url] but shows all entries of the Join table and not - like in the where condition only the not archived ones.