Join not working properly

[mridah]

I'm trying to work with joins but it's not giving the expected output.

This is the query I'm trying to achieve :

SELECT users.id as id, full_name as name, user_type as type, company_id as company, company.name as company_name FROM users INNER JOIN company ON company.id = users.company_id WHERE users.email = "email" AND users.password = "this_is_pass" AND users.active = 1 LIMIT 1

This query works fine but when I try to implement it in PDO, the company_name value is always NULL

This is my code:

$query = $this->$pdo->select(['users.id as id', 'full_name as name', 'user_type as type', 'company_id', 'company.name as company_name'])
                       ->from('users')
                       ->join(new Clause\Join('company',
                            new \FaaPz\PDO\Clause\Conditional('users.company_id', '=', 'company.id'),
                            'INNER'
                        ))
                       ->where(
                           new \FaaPz\PDO\Clause\Grouping("AND",
                               new \FaaPz\PDO\Clause\Conditional('users.email', '=', $user),
                               new \FaaPz\PDO\Clause\Conditional('users.password', '=', md5($password)),
                               new \FaaPz\PDO\Clause\Conditional('users.active', '=', 1)
                       ))
                       ->limit(new Clause\Limit(1));

        $stmt = $query->execute();
        $data = $stmt->fetchAll();
        echo $query->__toString();
        print_r($query->getValues());
        var_dump($data[0]);

The response of __toString() is:

SELECT users.id as id, full_name as name, user_type as type, company_id, company.name as company_name FROM users INNER JOIN company ON users.company_id = ? WHERE users.email = ? AND users.password = ? AND users.active = ? LIMIT ?

And, the response of getValues is:

Array
(
    [
  0
] => company.id
    [
  1
] => email
    [
  2
] => this_is_pass
    [
  3
] => 1
    [
  4
] => 1
)

This is happening most probably because PDO is evaluating statement

new \FaaPz\PDO\Clause\Conditional('users.company_id', '=', 'company.id'),

as

where users.company_id = "company.id", [NOTE string instead of column]

instead of

where users.company_id = company.id,

Am I doing anything wrong here @kwhat @FaaPz @delef ?

[sebastianroming]

@mridah I'm running into the same problem. Did you already find any solution?

[mridah]

@sebastianroming I was able to get it working but not with the query builder.

I had to use raw queries like the following:

$db = new \Slim\PDO\Database($db, $username, $password);
$stmt = $db->query('select * from emp;');
$stmt->execute();
$data = $stmt->fetchAll();

[GianniGntl]

Hi if you don't want third Clause\Conditional parameter beeing evaluated as a string to quote, you have to use

Clause\Raw

this allow you to specify a literal, that wil be considered AS-IS during statement construction. So your query section became:

->join(new Clause\Join('company', new Clause\Conditional('users.company_id', '=', new Clause\Raw('company.id')), 'INNER'))

(assuming the rest of query is correct); this is quite verbose, i suggest to use 'use FaaPz\PDO\Clause\Raw, Conditional' in front of your php script.

Documentation examples in GitHub are also wrong. Askinq developer to correct them, please.

Regards

[kwhat]

Updated the JOIN documentation to show Raw clause.