I'm working on a query where I pull data from multiple tables using left joins like this:
$query = "
SELECT
users.name,
users.email,
address.street,
address.city,
address.state,
address.zip
FROM users
LEFT JOIN(
SELECT
addresses.street,
addresses.city,
addresses.state,
addresses.zip,
`addresses.user_id `
FROM addresses
)
AS address
ON users.id = `address.user_id`
WHERE users.id = 1";
$mysql = new mysql(HOST, USER, PASS, DBNAME);
$result = $mysql->query($query)->fetch_object();
The results I get now I can access the results like this:
// get name $result->name; //get street address $result->street;
Since the query will eventually become something a little more complex than that. I would like to be able to access the data like this:
// get user name $result->user->name; // get the street address $result->address->street;
This will help make the data easier to read, since some of the table have similarly named fields.
Any help would be great thanks.
I am familiar with ORMs, and I'm currently using the Kohana framework. My interest is in cutting down on the actually number of queries run. The ORM in the Kohana framework calls a "SELECT *" for each table/model that you call. I'd prefer not to do that if I dont have to.
Running two separate queries(as shown in the example) is not that big of a deal, but in my real example i'll be pulling data from about 10 separate tables, so I'd rather not run separate queries to get the functionality i was describing
To answer your question in the comment, here's the query I would envision:
SELECT
users.name, users.email,
addresses.street, addresses.city, addresses.state, addresses.zip
FROM users
LEFT JOIN addresses
ON users.id = addresses.user_id
WHERE users.id = 1
Since the sub-select is at most a projection of the addresses table, it seems redundant.
As for the main question, I'm having a hard time coming up with anything that's elegant and non-hackish. In fact, the only things that do come to mind are downright ugly. You could, for instance, add prefixes to the column names in the query:
SELECT
users.name AS user_name, users.email AS user_email,
addresses.street AS address_street, ...
You'd have to parse the column names yourself. I suppose it wouldn't be too bad. Something like:
function rowToObject($row) {
$obj = new StdClass();
foreach ($row as $key => $val) {
$keys = explode('_', $key);
$o = $obj;
for ($i=0; count($keys) > 1; ++$i) {
$k = array_shift($keys);
if (! isset($o->{$k})) {
$o->{$k} = new StdClass();
}
$o = $o->{$k};
}
$o->{$keys[0]} = $val;
}
return $obj;
}
...
$result = rowToObject($mysql->query($query)->fetch_assoc());