Left join question

Left join question

maraboumarabou Posts: 8Questions: 2Answers: 0

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

This question has an accepted answers - jump to answer

Answers

  • allanallan Posts: 61,041Questions: 1Answers: 9,955 Site admin

    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

  • maraboumarabou Posts: 8Questions: 2Answers: 0

    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

  • allanallan Posts: 61,041Questions: 1Answers: 9,955 Site admin

    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

  • maraboumarabou Posts: 8Questions: 2Answers: 0

    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?

  • allanallan Posts: 61,041Questions: 1Answers: 9,955 Site admin
    Answer ✓

    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

  • maraboumarabou Posts: 8Questions: 2Answers: 0

    Thanks a lot Allan! :)

Sign In or Register to comment.