Databases
StackOverflow2013
Postsdbo
POStruggling With OOP Concept
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POStruggling With OOP Concept
    primarykey
    data
    text
    <p>I'm really struggling with a recurring OOP / database concept.</p> <p>Please allow me to explain the issue with pseudo-PHP-code.</p> <p>Say you have a "user" class, which loads its data from the <code>users</code> table in its constructor:</p> <pre><code>class User { public $name; public $height; public function __construct($user_id) { $result = Query the database where the `users` table has `user_id` of $user_id $this-&gt;name= $result['name']; $this-&gt;height = $result['height']; } } </code></pre> <p>Simple, awesome.</p> <p>Now, we have a "group" class, which loads its data from the <code>groups</code> table joined with the <code>groups_users</code> table and creates <code>user</code> objects from the returned <code>user_id</code>s:</p> <pre><code>class Group { public $type; public $schedule; public $users; public function __construct($group_id) { $result = Query the `groups` table, joining the `groups_users` table, where `group_id` = $group_id $this-&gt;type = $result['type']; $this-&gt;schedule = $result['schedule']; foreach ($result['user_ids'] as $user_id) { // Make the user objects $users[] = new User($user_id); } } } </code></pre> <p>A group can have any number of users.</p> <p>Beautiful, elegant, amazing... on paper. In reality, however, making a new group object...</p> <pre><code>$group = new Group(21); // Get the 21st group, which happens to have 4 users </code></pre> <p>...performs 5 queries instead of 1. (1 for the group and 1 for each user.) And worse, if I make a <code>community</code> class, which has many groups in it that each have many users within them, an ungodly number of queries are ran!</p> <p><strong>The Solution, Which Doesn't Sit Right To Me</strong></p> <p>For years, the way I've got around this, is to not code in the above fashion, but instead, when making a <code>group</code> for instance, I would join the <code>groups</code> table to the <code>groups_users</code> table to the <code>users</code> table as well and create an array of user-object-like arrays within the <code>group</code> object (never using/touching the <code>user</code> class):</p> <pre><code>class Group { public $type; public $schedule; public $users; public function __construct($group_id) { $result = Query the `groups` table, joining the `groups_users` table, **and also joining the `users` table,** where `group_id` = $group_id $this-&gt;type = $result['type']; $this-&gt;schedule = $result['schedule']; foreach ($result['users'] as $user) { // Make user arrays $users[] = array_of_user_data_crafted_from_the_query_result; } } } </code></pre> <p>...but then, of course, if I make a "community" class, in its constructor I'll need to join the <code>communities</code> table with the <code>communities_groups</code> table with the <code>groups</code> table with the <code>groups_users</code> table with the <code>users</code> table.</p> <p>...and if I make a "city" class, in its constructor I'll need to join the <code>cities</code> table with the <code>cities_communities</code> table with the <code>communities</code> table with the <code>communities_groups</code> table with the <code>groups</code> table with the <code>groups_users</code> table with the <code>users</code> table.</p> <p>What an unmitigated disaster!</p> <p><strong>Do I have to choose between beautiful OOP code with a million queries VS. 1 query and writing these joins by hand for every single superset? Is there no system that automates this?</strong></p> <p>I'm using CodeIgniter, and looking into countless other MVC's, and projects that were built in them, and cannot find a single good example of anyone using models without resorting to one of the two flawed methods I've outlined.</p> <p>It appears this has never been done before.</p> <p>One of my coworkers is writing a framework that does exactly this - you create a class that includes a model of your data. Other, <strong>higher</strong> models can <strong>include</strong> that single model, and it crafts and automates the table joins to create the <strong>higher</strong> model that includes object instantiations of the <strong>lower</strong> model, all in a <strong>single query</strong>. He claims he's never seen a framework or system for doing this before, either.</p> <p><strong>Please Note:</strong> I do indeed always use separate classes for logic and persistence. (VOs and DAOs - this is the entire point of MVCs). I have merely combined the two in this thought-experiment, outside of an MVC-like architecture, for simplicity's sake. Rest assured that this issue persists regardless of the separation of logic and persistence. I believe <a href="http://webadvent.org/2011/a-stitch-in-time-saves-nine-by-paul-jones" rel="nofollow">this article</a>, introduced to me by James in the comments below this question, seems to indicate that my proposed solution (which I've been following for years) is, in fact, what developers currently do to solve this issue. This question is, however, attempting to find ways of <strong>automating</strong> that exact solution, so it doesn't always need to be coded by hand for every superset. From what I can see, this has never been done in PHP before, and my coworker's framework will be the first to do so, unless someone can point me towards one that does.</p> <p>And, also, of course I never load data in constructors, and I only call the load() methods that I create when I actually need the data. However, that is unrelated to this issue, as in this thought experiment (and in the real-life situations where I need to automate this), I <strong>always</strong> need to eager-load the data of <strong>all subsets of children</strong> as far down the line as it goes, and <strong>not</strong> lazy-load them at some future point in time as needed. The thought experiment is concise -- that it doesn't follow best practices is a moot point, and answers that attempt to address its layout are likewise missing the point.</p> <p><strong>EDIT : Here is a database schema, for clarity.</strong></p> <pre><code>CREATE TABLE `groups` ( `group_id` int(11) NOT NULL, &lt;-- Auto increment `make` varchar(20) NOT NULL, `model` varchar(20) NOT NULL ) CREATE TABLE `groups_users` ( &lt;-- Relational table (many users to one group) `group_id` int(11) NOT NULL, `user_id` int(11) NOT NULL ) CREATE TABLE `users` ( `user_id` int(11) NOT NULL, &lt;-- Auto increment `name` varchar(20) NOT NULL, `height` int(11) NOT NULL, ) </code></pre> <p>(Also note that I originally used the concepts of <code>wheel</code>s and <code>car</code>s, but that was foolish, and this example is much clearer.)</p> <p><strong>SOLUTION:</strong></p> <p>I ended up finding a PHP ORM that does exactly this. It is <a href="http://laravel.com/docs/eloquent" rel="nofollow">Laravel's Eloquent</a>. You can specify the relationships between your models, and it intelligently builds optimized queries for eager loading using syntax like this:</p> <pre><code>Group::with('users')-&gt;get(); </code></pre> <p>It is an absolute life saver. I haven't had to write a single query. It also doesn't work using joins, it intelligently compiles and selects based on foreign keys.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USLeng
    1. USLeng
    plurals
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POStruggling With OOP Concept
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POStruggling With OOP Concept
      1. USMajor Productions
      1. VTFavorite
    3. VO
      singulars
      1. POStruggling With OOP Concept
      1. This table or related slice is empty.
      1. VTUpMod
    1. COIf you think about it, you could only have 3 queries. 1 to get city dealerships, 2 to get dealership cars, and 1 to get car wheels. How you retrieve the data is up to you, but you can still have elegant OOP code to reflect the objects.
      singulars
      1. POStruggling With OOP Concept
      1. USKenny Thompson
    2. CO@KennyThompson I suspect that dealership would actually be closer to something like a [repository](http://martinfowler.com/eaaCatalog/repository.html)-like structure. Just because your DB contains 10'000 cars, does not mean that it is practical to turn them all in objects.
      singulars
      1. POStruggling With OOP Concept
      1. UStereško
    3. CO@Kenny No, you only need 1 query to get a city (and -all- of its contained elements). You simply perform joins. However, that is not OOP.
      singulars
      1. POStruggling With OOP Concept
      1. USLeng
 

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