How do I build this mysql join query properly in cakephp?

Question

First of all, versions: cakephp -> 1.3.15, MySql -> 5.5.38, php -> 5.5.9-1ubuntu4.4 (output from phpversion()).

Note: I am switching table names and values for generic names. Perhaps this will help my own understanding as I write this, and I hope it will be clear to the readers.

Query I am trying to attain:

SELECT `a`.*
FROM `a`
INNER JOIN b
ON `a`.`foreign_key_non_unique_id` = 10
AND `b`.`email` = 'test@test.com'
LIMIT 1

Cakephp code I have:

$table_a_row= $this->a->find('first',
    array('conditions' => array(
        'a.foreign_key_non_unique_id' =>
            $foreign_key_non_unique_id_var['foreign_key_table']['id'],
        'bb.email' =>
            $value['askerEmail']),
        'joins' => array(
            'table' => 'b',
            'alias' => 'bb',
            'type'  => 'inner',
            'conditions' => 'bb.id = a.b_foreign_key_id')));

Query cakephp spits out:

SELECT `aa`.*
FROM `a` AS `aa` b bb
inner bb.id = aa.b_foreign_key_id
WHERE `aa`.`foreign_key_non_unique_id` = 10 
AND `bb`.`email` = 'test@test.com'    
LIMIT 1

PS. I normally don't ask questions, but joins are the bane of my existence. I understand them, but creating them within new frameworks is tiresome.

Additional useful URL: Diagram of join statements, for those interested.

Edit 1: My model relating to this problem is defined as this:

class aa extends AppModel {
    var $name = 'aa';
    var $useDbConfig = 'db';
    var $recursive = 1;
    var $validate = array(
        'foreign_key_non_unique_id_var' => (
            'notempty' => array(
                'rule' => array('notempty'),
                'message' => 'Foreign Key Id: This is a required field'
            )
        ),
        'b_foreign_key_id' => array(
            'notempty' => array(
                'rule' => array('notempty'),
                'message' => 'B Foreign Key Id: This is a required field'
        )
    )
    var $belongsTo = array(
        //foreign_key_non_unique_id_var table
        'Table' => array(
            'className' => 'Table',
            'foreignKey' => 'table_id',
            'conditions' => '',
            'fields' => '',
            'order' => ''
        )
    )
    var $hasMany = array(
        //unrelated tables
    );
}


class bb extends AppModel {
    var $name = 'bb';
    var $useDbConfig = 'db';
    var $primaryKey = 'id';
}

Also, before the cakephp query code, I have this:

$this->a->recursive = -1;

Edit 2: The title of my first model object was wrong, when comparing to "a". It is actually "aa", as well as the name variable inside of it. Same goes for model object b (now is properly labelled as "bb").

This is very strange, because `aa` doesn't appear anywhere in the code you've shown, but it's in the generated query. Any chance this is a transcription error on your part, since you've manually changed all the table and variable names? — Greg Schmidt, Apr 27 '18 at 15:26
@GregSchmidt It's possible this is a transcription error on my part. After reviewing my code, I see that my model should actually be titled "aa", with name "aa". I think that's where cakephp is getting the AS part of the join statement from. I will edit my question to fix this. — Millar248, Apr 27 '18 at 15:41
That's some, let's say, very creative indentation style ;) I'd bet you'll spot the problem if you align things properly, and compare it to **https://book.cakephp.org/1.3/en/The-Manual/Developing-with-CakePHP/Models.html#joining-tables**, at least with regards to the invalid SQL. — ndm, Apr 27 '18 at 19:40
@ndm Oh wow. I see it. I'm missing an array(). Ugh. I will try using this piece of information next week to solve the issue, but alas, it's Friday, and I already hacked my way through the situation. Time to go home :D — Millar248, Apr 27 '18 at 20:44

score -1 · Accepted Answer · answered Apr 27 '18 at 15:08

So, to answer my own question, I avoided cakephp entirely.

My solution has been to create a function inside my model that executes the query directly. This works because I already have the precise mysql syntax, and working with cakephp is proving to be too confusing in this case.

This alternate question and answer provided insight into my problem: SO Question

I would have liked to use cakephp's engine to generate this join statement, but perhaps my version of cakephp is too old, or perhaps I don't know enough about the framework yet.

Cheers.

How do I build this mysql join query properly in cakephp?

1 Answers1