BUG: Using join with Mappings causes PDO statement to become false.

[b-hayes]

VERSION USED "elvanto/picodb": "2.1.0"

What happened: When using a mapping like so:

$groupMembers = (new Definition('list_member_contacts', ['list_id', 'contact_id', 'created_at', 'updated_at']))
            ->withColumns('contact_id', 'list_id', 'leader')
            ->withDeletionTimestamp('deleted_at');

$mapping = $this->mapper->mapping($groupMembers);

Fetch all works fine, but if we add a join before a fetch all:

            ->join('contacts', 'id', 'contact_id')->fetchAll();

Results in:

Error: Call to a member function fetchAll() on bool

/app/vendor/elvanto/picodb/lib/PicoDb/Table.php:314
/app/vendor/tithely/picomapper/src/Mapping.php:82

Note the error happens internal. I traced it to vendor/elvanto/picodb/lib/PicoDb/StatementHandler.php:240 and added a dump of the errors print_r($pdoStatement->errorInfo()); die(); and saw only this:

Array
(
    [0] => 00000
    [1] =>
    [2] =>
)

Expected I expected: Now If I do the same thing with a Table object instead everything works as it should:

$q = $this->db->table('list_member_contacts')
            ->join('contacts', 'id', 'contact_id');
        var_dump($q->findAll());die();//this works! returns array of data

I expect the mapper to work the same.

[b-hayes]

Just had the same error but this time was caused by adding an ->eq condition to the Table object instead. https://github.com/tithely/people/pull/1182/commits/29baef8e323f309360dd44b82a560f7762eeeb00

Some debug info. I change the table.php to extract the SQL and values and dump them if the statement became false.

$sql = $this->buildSelectQuery();
        $values = array_merge($this->conditionBuilder->getValues(), $this->aggregatedConditionBuilder->getValues());
        $rq = $this->db->execute($sql, $values);
        if (!$rq){
            var_dump($sql, $values);die();
        }
        $results = $rq->fetchAll(PDO::FETCH_ASSOC);

And this is what it gave me:

string(668) "SELECT list_member_contacts.*, c.*, e.email_address e_email_address, e.messaging_allowed e_messaging_allowed, e.invited_at e_invited_at, e.decided_at e_decided_at, p.phone_number p_phone_number, p.country_code p_country_code, p.messaging_allowed p_messaging_allowed, p.invited_at p_invited_at, p.decided_at p_decided_at FROM `list_member_contac
ts` LEFT JOIN contacts AS c ON `c`.`id`=`list_member_contacts`.`contact_id` LEFT JOIN contact_emails AS e ON `e`.`contact_id`=`list_member_contacts`.`contact_id` LEFT JOIN contact_phone_numbers AS p ON `p`.`contact_id`=`list_member_contacts`.`contact_id`  WHERE `list_id` = ? AND `contact_id` = ?    ORDER BY `last_name` ASC"
array(2) {
  [0]=>
  string(36) "a09d2d17-cd51-4885-a97f-5732ffe898a4"
  [1]=>
  string(36) "9A66A5CD-0CB3-66B9-FFD3-DE7270541132"
}

[b-hayes]

Update: based on the resulting SQL it appears it should have been throwing an error for an ambiguous table column reference. So what may need to be done is to simply do what I have done above but throw an exception with the SQL inside it so we can see the problem.

This is what I am using in my local copy:

    public function findAll()
    {
        $sql = $this->buildSelectQuery();
        $values = array_merge($this->conditionBuilder->getValues(), $this->aggregatedConditionBuilder->getValues());
        $rq = $this->db->execute($sql, $values);
        if (!$rq){
            throw new \Exception(
                "The following statement failed to execute: $sql PARAMS: "
                . var_export($values, true)
            );
        }
        $results = $rq->fetchAll(PDO::FETCH_ASSOC);

        if (is_callable($this->callback) && ! empty($results)) {
            return call_user_func($this->callback, $results);
        }

        return $results;
    }

[CoreyRDean]

@b-hayes @joshmcrae this should be fixed with this PR in tithely/picomapper https://github.com/tithely/picomapper/pull/18