How to load related objects efficiently in php/mysql?

Question

The situation

I'm using PHP & PDO with mysql and would like to query 1 table as well as rows that it points to in another table.

I have a class like:

class Pet {
    public $id;
    public $name;
    public $type;
    public $owner;
}

and a table

pet
id | name | type | owner_id
0  | jim  | cat  | 87
1  | gale | dog  | 50
2  | foxy | cat  | 60

You can see that Pet refers to an Owner. This class is like:

class Person {
    public $id;
    public $name;
    public $pets;
}

And the table

person
id | name 
87 | Joshua
50 | Bob
60 | Cynthia

Now, I want to get all the cats, with each property set, including the owner.

Solution 1

If i have a query like:

SELECT pet.*, person.* FROM pet
JOIN person
    ON person.id = pet.ownerId
WHERE pet.type LIKE 'cat'

With the associative array returned, this will create a conflict on both id and name columns.

I have two options
1.) Use the numeric indices returned to map to the Pet objects and related Person objects
2.) prefix table column names & use the associative array returned to instantiate the objects

Solution 2

Or I can do a query like:

SELECT * FROM pet WHERE pet.type LIKE 'cat'

and get a resulting associative array. I can loop over and get a list of all the owner ids and do:

SELECT * FROM person WHERE person.id IN (LIST_OF_IDS)

then instantiate the Person objects and assign them to the $owner property of my Pet objects.

complaints

Solution 1.1 (numeric indices)

This will technically work, but then I have to map numeric entries to the correct property names, which seems very prone to error, especially with any changes to DB schema.

Solution 1.2 (prefixes)

This will work pretty easily, but upon reading up, prefixing column names is discouraged. Also... I don't really want to prefix my column names.

Solution 2

This will work, but does not seem performant.

Question

Are there other options for this that I'm missing?

I tried figuring out a way to prefix the column name in the SELECT statement (which would be fine, as it wouldn't affect the actual database), but it does not seem to be possible.

ArendE · Accepted Answer · 2019-07-18 03:35:45Z

2

As far as I know you can't change or prefix all column names (person.* as person.*) at once in MySQL, only single columns as mentioned in other answers. Since you've mentioned using the PDO as well, consider other approaches, two of them below.

1.3: Use PDO statement getColumnMeta()

This will ask the database additional metadata about the numeric column:

$qry = "SELECT pet.*, person.* FROM pet JOIN person ON person.id = pet.owner_id WHERE pet.type LIKE 'cat'";
$stmt = $dbh->prepare($qry);
$stmt->execute();       
while($row = $stmt->fetch(PDO::FETCH_NUM)) {
    foreach($row as $key => $value) {
        $meta = $stmt->getColumnMeta($key);
        echo $meta['table'] . '.' . $meta['name'] . ': ' . $value . PHP_EOL;
    }
}

/* Output: 
pet.id: 2
pet.name: foxy
pet.type: cat
pet.owner_id: 60
person.id: 60
person.name: Bob
..
*/

1.4: Use PDO statement fetch with fetch_style PDO::FETCH_NAMED

This will make fields that have the same name become an array. Still be aware of the ordering: SELECT pet.*, person.* vs. SELECT person.*, pet.*.

$qry = "SELECT pet.*, person.* FROM pet JOIN person ON person.id = pet.owner_id WHERE pet.type LIKE 'cat'";
$stmt = $dbh->prepare($qry);
$stmt->execute();       
while($row = $stmt->fetch(PDO::FETCH_NAMED)) {
    echo $row['name'][1] . ' owns a ' . $row['type'] . ' named ' . $row['name'][0] . PHP_EOL;
}

/* Output:
Bob owns a cat named foxy
Joshua owns a cat named jim
*/

answered Jul 18, 2019 at 3:35

ArendE

9971 gold badge8 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Reed Over a year ago

Thanks. I came accross getColumnMeta but guess I didn't look at it long enough. Hadn't noticed PDO::FETCH_NAMED. I think I'll write a little function with getColumnMeta to split results by table name and then go that route.

ArendE Over a year ago

Good luck with it! :) Perhaps this user note might come in useful for it.

Don't Panic Over a year ago

Nice answer! I had completely forgotten about FETCH_NAMED

Don't Panic · Accepted Answer · 2019-07-16 21:49:08Z

2

You can alias the columns in one or both of the tables so they won't be ambiguous, for example:

SELECT pet.*, person.name AS owner_name
FROM pet JOIN person ON person.id = pet.ownerId
WHERE pet.type LIKE 'cat'

(You don't need to select the id from the person table since it will already be included as ownerId.)

answered Jul 16, 2019 at 21:49

Don't Panic

41.9k11 gold badges70 silver badges87 bronze badges

4 Comments

Reed Over a year ago

Yeah, that's a... very valid option. I was hoping not to alias all my columns... nor explicitly name them. Though, I've also read that table.* is bad practice, so I guess I should get over it (and be explicit & alias)? lol

Don't Panic Over a year ago

IMO using * isn't bad practice if you really do need every column, people just seem to use it haphazardly and it's wasteful to select the entire row if you only need a couple of columns from it. In my experience with things like this you often don't need all the columns from one side of the join anyway.

Don't Panic Over a year ago

If the table you're joining doesn't have too many columns I think it's not much of a pain to do it this way, just aliasing selected columns on one side. And if the table does have so many columns that it is a pain, that might be an indication another problem.

Reed Over a year ago

Very good points. Probably will go this aliasing route... because then I don't have to mess with my schema or prefix as a general rule. I could easily write a function like makeSelect(string $table, array $columns, string $prefix) and get back something like table.col1 as pref_col1, table.col2 as pref_col2. and then another to remove those prefixes in the resultset if I so wish.

Milorad · Accepted Answer · 2019-07-16 21:33:30Z

0

What I see as best practice is not to use same column names in different tables, that should be advised. Column name should be descriptive to its entity. In this case Pet.id should be Pet.pet_id and so on.

Hope this helps.

Thanks,

answered Jul 16, 2019 at 21:33

Milorad

213 bronze badges

1 Comment

Reed Over a year ago

I have mixed feelings about this approach. Prefixing my column names with the table name would... do what I need it to do, but is redundant, since I'm just repeating the table name as a prefix to the column name

Collectives™ on Stack Overflow

How to load related objects efficiently in php/mysql?

The situation

Solution 1

Solution 2

complaints

Solution 1.1 (numeric indices)

Solution 1.2 (prefixes)

Solution 2

Question

3 Answers 3

3 Comments

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

The situation

Solution 1

Solution 2

complaints

Solution 1.1 (numeric indices)

Solution 1.2 (prefixes)

Solution 2

Question

3 Answers 3

3 Comments

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related