ID not populating with this SQL select

[Josh5A]

With this sql select I'm getting behavior I didn't expect:

SELECT *
FROM ISSUE i
LEFT JOIN REMOTEID r
ON i.id = r.issue_id
AND r.service_name = 'AskYourPdf'
WHERE r.issue_id IS NULL
AND i.publication_id = 1
LIMIT 1

Output is:

Array
(
    [0] => Array
        (
            [id] => 
            [filename] => Myfile.pdf
            [publication_id] => 1
            [volume] => 01
            [number] => 01
            [chronology_type] => month
            [chronology_year] => 1987
            [chronology_month] => 10
            [service_name] => 
            [remoteid] => 
            [issue_id] => 
        )
)

I would expect id to be populated. When I modify the sql this way, it works:

SELECT *, i.id
FROM ISSUE i
LEFT JOIN REMOTEID r
ON i.id = r.issue_id
AND r.service_name = 'AskYourPdf'
WHERE r.issue_id IS NULL
AND i.publication_id = 1
LIMIT 1

Output is:

Array
(
    [0] => Array
        (
            [id] => 1
            [filename] => Myfile.pdf
            [publication_id] => 1
            [volume] => 01
            [number] => 01
            [chronology_type] => month
            [chronology_year] => 1987
            [chronology_month] => 10
            [service_name] => 
            [remoteid] => 
            [issue_id] => 
        )
)

Is this expected behavior? When I run the first query through MySQL directly rather than through RedBean it works as I expect.

[Lynesth]

How do you call that query?

[Josh5A]

$query = "SELECT *, i.id
FROM ISSUE i
LEFT JOIN REMOTEID r
ON i.id = r.issue_id
AND r.service_name = '$serviceName'
WHERE r.issue_id IS NULL
AND i.publication_id = $pubId
LIMIT $numberOfFilesToUpload";

$rows = R::getAll($query);
$issuesToUpload = R::convertToBeans('issue', $rows);

[Lynesth]

Both tables have an id column. If you don't specify anything in the SELECT clause, SQL will use the one from the last table to be joined. In your case it's going to the use the one from the remoteid table, which - if I properly understood what you're trying to achieve there - is very likely going to be null. By specifying i.id in your SELECT, you are telling the db that this is the id column you want.

[Lynesth]

Moreover (I have not tested this and haven't written any Redbean code in a while) you might be able to achieve the result you want directly using:

$issuesToUpload = R::find(
    'issue',
    ' SELECT i.*
      FROM issue i
          LEFT JOIN remoteid r
          ON i.id = r.issue_id AND r.service_name = ?
      WHERE r.issue_id IS NULL
          AND i.publication_id = ?
      LIMIT ? ',
    [ $serviceName, $pubId, $numberOfFilesToUpload ]);