Re: [Javuh-develop] Left join question

[why t. l. s. <ja...@wh...>]

Heya Nigel--

Does that LEFT JOIN query work for you or are you looking for a RIGHT JOIN?

Example:

mysql> create table test1 ( id int4, name char(20) );
mysql> create table test2 ( id int4, name char(20) );
mysql> insert into test1 values (2, 'Hello' );
mysql> insert into test1 values (3, 'Goodbye');
mysql> insert into test1 values (4, 'Forever');
mysql> insert into test2 values (4, 'Forever');

-- LEFT JOIN test2 -> test1
mysql> select * from test2 left join test1 on test1.id = test2.id;
+------+---------+------+---------+
| id   | name    | id   | name    |
+------+---------+------+---------+
|    4 | Forever |    4 | Forever |
+------+---------+------+---------+
1 row in set (0.00 sec)

-- LEFT JOIN test1 -> test2
mysql> select * from test1 left join test2 on test1.id = test2.id;
+------+---------+------+---------+
| id   | name    | id   | name    |
+------+---------+------+---------+
|    2 | Hello   | NULL | NULL    |
|    3 | Goodbye | NULL | NULL    |
|    4 | Forever |    4 | Forever |
+------+---------+------+---------+
3 rows in set (0.00 sec)

-- RIGHT JOIN test2 -> test1
mysql> select * from test2 right join test1 on test1.id = test2.id;
+------+---------+------+---------+
| id   | name    | id   | name    |
+------+---------+------+---------+
| NULL | NULL    |    2 | Hello   |
| NULL | NULL    |    3 | Goodbye |
|    4 | Forever |    4 | Forever |
+------+---------+------+---------+
3 rows in set (0.00 sec)

So the right join returns data, even when test2 has no content.  Is that what you're looking for?

The trouble with mapping zero-to-many relationships in Psychoo is that every row of data in Psychoo represents an object -- a PHP instance.  So data loaded from the database is accessed like this:

  $boxes = $psychoo->load_all( array( 'box', 'content' ) );
  foreach( $boxes as $box )
  {
     foreach( $box->content as $c )
     {
        // Handle the 'content' object
     }
  }

When Psychoo sees null rows, it creates no objects, as there are no means for creating objects from NULL data at the moment.  It can't assign properties to a $box which is null.  Of course, I want to see Psychoo accomodate any kind of data structure.  At the moment, we just need to find a suitable nomenclature for representing null objects.

The other problem here is that the load_all defaults to a LEFT JOIN.  Starting with the head table in your array, it will add LEFT JOINs to the query, as it scans your object list.  The only exception is when your head table contains a foreign key to the primary key of a table deeper in the list.  Often these are INNER JOINed.

So I imagine that we will keep load_all as it is, but add RIGHT JOINs to the new load queries.  I'll have it ready by 1.1.  In the meantime, I'll encourage people to use straight SQL.  I wish Psychoo was all things to all people, but it's really a framework for eliminating the work that is the most common and most mundane.  I know... I know... ALL SQL is COMMON and MUNDANE.  ;)

_why

On Tue, 2 Jul 2002 14:28:23 +0100
"Nigel Armstrong" <Nig...@te...> wrote:
> Hi,
> 
> Is there any way in the current release of javuh to generate a query like
> 'SELECT * FROM box LEFT JOIN content ON box.id = content.box_id'? The query
> generated by default by $psychoo->load_all(array('box', 'content')) returns no
> data when a box has no content, which is not what is wanted when you are
> modelling a zero to many relationship.
> 
> Currently I am executing separate queries and merging the results; I could also
> hand code the SQL but that would somewhat defeat the purpose of using Javuh.
> 
> Nigel