Left join question

marabou

Hi everyone,

I am trying to implement a one-to-many left join with an 'AND' (instead of where) clause.

My primary table is customers (PK = customer_id)
The joined table is files (PK = file_id)

The query I need to display is

SELECT * FROM customers
LEFT JOIN files on customers.customer_id = files.entity_id AND entity_type='customer'.

If AND is swapped with WHERE, only customers that have associated files are returned. I need to have all customers returned.

Is this query type possible with the Mjoin class?

I hope the above makes sense, thank you

allan

With an Mjoin, currently no, that is not possible at this time. You can use a left join with an arbitarily complex expression, but Mjoin is a simple matching condition.

Allan

marabou

Thank you for your reply Allan,

I tried the left join with a complex however it returns as many customer entries (rows) as the files are.

I need it to return just one row per customer, regardless of how many files there are attached.

You are suggesting that the mjoin won't work at this time.

Is there a way of achieving this one-to-many relationship with a complex left join?

Your example here is almost exactly what I need, minus the 'AND' condition.

Any ideas apart from redesigning the database?

Thanks, Nico

allan

Looking at your query again, what table is entity_type on? If it is the files table, you could apply that as a where condition to the Mjoin, since it isn't actually part of the join condition. I.e. I think your query above could be rewritten as:

SELECT * FROM customers
LEFT JOIN files on customers.customer_id = files.entity_id
WHERE entity_type='customer'.

Is that fair to say?

Allan

marabou

Hi Allan,

I have changed the code to make use of the mjoin and indeed it returns one row per customer, thanks.

The issue now is that I get an error when I use the where statement, here is the code:

    ->join(
        Mjoin::inst('entity_files')
            ->link('customers.customer_id', 'entity_files.entity_id')
            ->where('entity_files.entity_type', '=', 'person') // Issue here?
            ->order('file_name asc')
            ->validator('entity_files[].file_id', Validate::mjoinMaxCount(4, 'No more than four files please'))
            ->fields(
                Field::inst('file_name')
                    ->validator(Validate::required())
                    ->options(Options::inst()
                        ->table('entity_files')
                        ->value('file_id')
                        ->label('file_name')
                    ),
                Field::inst('entity_type'),
                Field::inst('upload_date'),
                Field::inst('file_id')
            )
    )

and the error is:

DataTables warning: table id=example - An SQL error occurred: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'person '=' AND customers.customer_id IN ('13677', '16932', '6381', '12757', ' at line 1

Any thoughts?

allan

Any thoughts?

Yup. Some even relate to this ;-)

->where('entity_files.entity_type', '=', 'person')

the arguments are wrong. They should be:

->where('entity_files.entity_type', 'person')

The header for the function is:

public function where($key = null, $value = null, $op = '=')

Allan

marabou

Thanks a lot Allan!