Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Because no answers, I'll try to describe how I solved this. This is a toy solution.</p> <p>Queries to the database are done in a little language I called QQL, this was inspired by Doctrine 2 <a href="http://docs.doctrine-project.org/projects/doctrine-orm/en/2.1/reference/dql-doctrine-query-language.html" rel="nofollow">DQL</a>.</p> <pre><code>SELECT * FROM user JOIN user order JOIN order payment WHERE user.name=? </code></pre> <p>This gets parsed and an SQL statement can be built. The parse tree is also used to map the result set back to an object graph.</p> <p>Each model has a description of 'relations' to other models. So User has a one-to-many to Order, Order has a one-to-many to Payment. The description is in an array containing the model name, the primary key and foregin key names.</p> <p>To build the JOIN:</p> <pre><code>JOIN user order </code></pre> <p>Inspect the User model, get the table name 'users', then find the relationship 'order', get the Order table name 'orders' and joining keys. Use this to build:</p> <pre><code>JOIN orders o ON o.user_id=users.id </code></pre> <p>Once the query has run and results are returned to build the object graph. What I did was get all the distinct models used in the query (in this case User, Order and Payment) then for each row Hydrate each one:</p> <pre><code>// query DB and get results into an array called $rows foreach ($rows as $row) { foreach (array('User', 'Order', 'Payment') as $model) { $o = new $model; $o-&gt;hydrate($row); // inspect primary key - have we got this object already? store or throw away } } </code></pre> <p>My hydrate method was extremely thin (quick) because a lot of the objects that are built will be duplicates and get removed. From the result set in my question you can see that User('Jeff') will be built 5 times, 4 of which are duplicates and will be discarded.</p> <p>Once the results have been read, there are 3 lists of objects. Users, Orders and Payments. These are passed to a Graph Builder along with the parse tree.</p> <p>The Graph Builder uses the parse tree to look at the relationships. Starting at the 'root' model (determined by the "FROM user"), inspect the parsed QQL to find the JOIN requested (User->id TO Order->user_id) adds the Orders to the User->orders array. It then does the same with (Order->id TO Payment->order_id).</p> <p>The result is:</p> <pre><code>$user-&gt;name == 'jeff' $user-&gt;orders[0]-&gt;item == 'spring' $user-&gt;orders[1]-&gt;item == 'jam' $user-&gt;orders[2]-&gt;item == 'car' $user-&gt;orders[2]-&gt;payments[2]-&gt;date_time == '19-12-2012' </code></pre> <p>I ended up with four main classes, the ORM, a Model_Base that all models extend (this makes sure each model has a 'table name', 'columns' and 'relations'), a QQLParser and a Graph Builder. The ORM class was by far the largest at nearly 200 lines.</p> <p><strong>Conclusion.</strong> It works. It feels similar to Doctrine 2, (so my eventual transition to using Doctrine 2 will be less painful). It could be more efficient. Results of profiling a test page reading in a few thousand objects, by far the slowest parts were the SQL query (3ms) and freeing the mysqli results (1ms) including the model classes (0.7ms). Writing it was fun and only took a day.</p>
    singulars
    1. This table or related slice is empty.
    1. POHow do PHP ORMs map results to objects (hydration)
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USwe-all-fall-down
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POHow do PHP ORMs map results to objects (hydration)
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload