Is it possible to send a value together with Sql JOIN and AND operator?

Questions Components & MVC
1

I want to send a language id value that is valid only in the joined table.

        $sql = new Sql($this->adapter);
        $select = $sql->select();
        $select->from('users');
        $select->columns([
            'user_id',
        ]);
        $select->join(['s' => 'specifications'], 'u.client_id = s.client_id AND u.user_id = s.consultant_id', 
                       ['specification_id'], $select::JOIN_LEFT);
        $select->join(['sl' => 'specifications_lang'], 
        	's.client_id = sl.client_id AND s.specification_id = sl.specification_id AND sl.lang_id = ?',
        	$select::JOIN_LEFT
        ); 
        $statement = $sql->prepareStatementForSqlObject($select);
        $resultSet = $statement->execute(['en']);

But quote identifiers not allow to send values into join clause.

SELECT `users`.`user_id` AS `user_id`, `s`.`specification_id` AS `specification_id`, `sl`.`left` AS `left` FROM `users` LEFT JOIN `specifications` AS `s` ON `u`.`client_id` = `s`.`client_id` AND `u`.`user_id` = `s`.`consultant_id` INNER JOIN `specifications_lang` AS `sl` ON `s`.`client_id` = `sl`.`client_id` AND `s`.`specification_id` = `sl`.`specification_id` AND `sl`.`lang_id` = `?`
2

If you want to call a function or add parameters then look at the allowed types of the second parameter of the method join. There you will find string and Laminas\Db\Sql\Expression. The last one will help you.

3

Thanks !. Do i need to write identifiers as manually or is there any solution for this ?

        $expression = new Expression(
            '`s`.`client_id` = `sl`.`client_id` AND `s`.`symptom_id` = `sl`.`symptom_id` AND `sl`.`lang_id` = ?',
            [LANG_ID]
        );        
        $select->join(['sl' => 'symptoms_lang'], $expression,['symptom_name', 'description' ], $select::JOIN_LEFT);
4

Please have a look at the “platform object”.

1 Like