JTable::load()

[Rafael Diaz-Tushman from Dioscouri]

Many tables, whether core tables or not, have more than one unique
index. However, if you want to load a row by one of the indices other
than the primary key, you either have to

1) override the JTable::load() method in your table's class (which is
unnecessary considering how simple this patch) or
2) re-write the query (No! Bad! DRY!)

So, I'd like to propose a small change to JTable::load() that won't
break anything currently using it, but will add the ability to load a
JTable object using another index.

Code:

function load( $oid=null, $reset = true, $key=null )
{
if (!empty($key))
{
// Check that $key is field in table
if ( !in_array( $key, array_keys( $this->getProperties() ) ) )
{
$this->setError( get_class( $this ).' does not have the field '.
$key );
return false;
}
$k = $key;
}
else
{
$k = $this->_tbl_key;
}

if ($oid !== null) {
$this->$k = $oid;
}

$oid = $this->$k;

if ($oid === null) {
return false;
}

if ($reset) {
$this->reset();
}

$db =& $this->getDBO();

$query = 'SELECT *'
. ' FROM '.$this->_tbl
. ' WHERE '.$k.' = '.$db->Quote($oid);

$db->setQuery( $query );

if ($result = $db->loadAssoc( ))
{
return $this->bind($result);
}
else
{
$this->setError( $db->getErrorMsg() );
return false;
}
}


Some possible uses for this include (but are not limited to):

1) Checking for existing users with a certain username
2) Checking for existing users with a certain email
3) Any other simple query on an existing JTable object using a key
other than the primary

Example usage:

$user = JTable::getInstance('User', 'JTable');
$user->load('raf...@dioscouri.com', true, 'email');

(yes, I know that in 1.6 the JTableUser::load() method is overridden,
but it wouldn't take much to change that method too...)

Cheers,
Rafael

[Rafael Diaz-Tushman from Dioscouri]

No replies to this. Is there no interest in it? Should I have
submitted this in another place or in some other way?

Cheers.
Rafael

On 21 Oct, 11:32, Rafael Diaz-Tushman from Dioscouri

[Louis Landry]

Rafael,

Sorry, I missed this one when it came around, probably in a bulk of things i scanned.  I have often thought similar things for the load method.  I'm all for it.

- Louis

--
Development Coordinator
Joomla! ... because open source matters.
http://www.joomla.org

[Rafael Diaz-Tushman]

Louis, I'm glad to hear.

I also have a class that (as is done with tablenested.php) could sit parallel to table.php.  It's called tablexref.php and we use it in extensions that have db tables with NO primary key, but instead have two indexes that, when combined, form a unique index (hence the cross reference in the name).  There are tables like this in the Joomla core, including:

__modules_menu

__schemas

__user_usergroup_map

The class allows you to treat xref tables in the exact same manner as single primary key tables.  

For example, if those tables above were using this class, you could use these familiar methods:

$table = JTable::getInstance('ModulesMenu', 'JTable');

$table->load( $moduleid, $menuid ); // returns the unique row in the DB

$table->save(); // if there's an existing row with these indexes, performs update, otherwise insert

$table->delete(); // deletes the row

Imagine using those method in admin::com_modules.model.store() rather than the hand-written queries that are currently in 1.6.

Is this something you'd be interested in?  If so, where should I submit the code (here, in one of the other Google Groups, in JoomlaForge, or somewhere else)?

Cheers.

--
Rafael Diaz-Tushman, President & CEO
Dioscouri Design: Form and Function
www.dioscouri.com
www.twitter.com/dioscouri

[Louis Landry]

Rafael,

I have also experimented with things like that.  I made a table class that could manage multi-mapped relationships too, but it was too complex for my liking.  I would like to see what you have.  You mind posting it at http://pastie.org/ while I figure out what the best way to approach that might be?

I am working on a way for us to have a playground to build and collaborate on code informally like that at j.org but it may be a couple of weeks yet before I can get that all sorted out.

Great initiative Rafael, are you by chance coming to the dev conference?

- Louis

[Rafael Diaz-Tushman]

Louis,

OK, I'll post the xref on pastie.  And yes, I'll be at the JDEV Conference in December.

Some quick notes on the class:

1) It's written for tables with two indexes that, when combined, form a unique index.  I have a class that supports an optional 3rd index, but I figured that would be too much and developers can just override this xref class when they have more than two indexes involved

2) When the JTable class is defined, it expects you to define those two indexes (the way you do the PK now...). At the bottom of the pastie, I have a sample class using the Xref

3) This class assumes the two indexes are integers, and forces them to be. If this needs to be changed, the dev would have to override the class 

I've never used pastie -- this should be fun.  Here is the link to the class: http://pastie.org/676656

Cheers.
--
Rafael Diaz-Tushman, President & CEO
Dioscouri Design: Form and Function
www.dioscouri.com
www.twitter.com/dioscouri

[Louis Landry]

Rafael,

I am going to be offline for pretty much the whole weekend while attending VooDoo Music Festival (http://thevoodooexperience.com/2009/index.php) ... will get to this after the weekend but didn't want you to think i was ignoring :)

- Louis

[Rafael Diaz-Tushman]

Oh, sounds fun!  Have a good time and we can talk about this next week.

Cheers!

--
Rafael Diaz-Tushman, President & CEO
Dioscouri Design: Form and Function
www.dioscouri.com
www.twitter.com/dioscouri

[Andrew Eddie]

Hi Rafael

It's a good idea because it's annoyed me in the past too.

May I suggest that $oid could be either numeric (in which case use the
primary key), or a named array. For example:

// Load by the PK
$table->load(123);

// Load by another field
$table->load(array('alias' => 'the-long-way-home'));

I think that just keeps it a bit neater.

Regards,
Andrew Eddie
http://www.theartofjoomla.com - the art of becoming a Joomla developer




2009/10/22 Rafael Diaz-Tushman from Dioscouri <rdiazt...@dioscouri.com>:

[Jeremy Huntwork]

On Nov 1, 2009, at 5:27 AM, Andrew Eddie wrote:
> May I suggest that $oid could be either numeric (in which case use the
> primary key), or a named array. For example:
>
> // Load by the PK
> $table->load(123);
>
> // Load by another field
> $table->load(array('alias' => 'the-long-way-home'));

I really like this suggestion (both Rafael's idea and what Andrew
added above). Making the $oid a mixed variable means that you can
limit the function to just one parameter, and you can use just that
one function for a table with any number of columns acting as keys.
This solves the issue you mentioned Rafael about possibly having a
table with three keys.

Awesome, wish I had thought of it. :-)

Jeremy Huntwork
LightCube Solutions
http://www.lightcubesolutions.com

[Rafael Diaz-Tushman]

Oh, very good idea.  I'll change it around and update the pastie.

--
Rafael Diaz-Tushman, President & CEO
Dioscouri Design: Form and Function
www.dioscouri.com
www.twitter.com/dioscouri

[Rafael Diaz-Tushman from Dioscouri]

Ok, Pastie updated to incorporate Andrew's great suggestion of using a
named array.

http://pastie.org/676656

Some nice results that came of this:

1) TableXref no longer needs to override Table::load() because
Table::load() supports multiple indexes
2) I had to add a 'delete' query type to query.php, so that it can
build more than just select queries (let me know if you want our
query.php file. It includes many fixes and additions to the query.php
in 1.6)
3) TableXref::delete() could (as with load) replace the Table::delete
() -- TableXref::delete() supports deletes using any number of
indexes, including only one -- but to do this, you'd need to move
TableXref::getKeyNames() to Table::getKeyNames()

Cheers!

--
Rafael Diaz-Tushman, President & CEO
Dioscouri Design: Form and Function
www.dioscouri.com
www.twitter.com/dioscouri

On 1 Nov, 10:17, Rafael Diaz-Tushman <rdiaztush...@dioscouri.com>
wrote:

[Andrew Eddie]

One more modification you can make. If the array contain "multiple"
key value pairs, check them all, eg:

// set up an match on the named array
$wheres = array();
foreach ($oid as $k => $v) {
$wheres[] = $db->NameQuote( $k ).'='.$db->Quote( $v );
}

then

. ' WHERE '.implode( ' AND ', $wheres );

Just found that code snippet laying around in my code journal.

Regards,
Andrew Eddie
http://www.theartofjoomla.com - the art of becoming a Joomla developer

2009/11/2 Rafael Diaz-Tushman from Dioscouri <rdiazt...@dioscouri.com>:

[Rafael Diaz-Tushman]

Thanks, Andrew.  I'm actually making a conscious effort to use the query builder rather than manually building the query strings.  There are only two small changes to be made (to insertObject & updateObject) to get the entire class using it.

Cheers.

--
Rafael Diaz-Tushman, President & CEO
Dioscouri Design: Form and Function
www.dioscouri.com
www.twitter.com/dioscouri

[Rafael Diaz-Tushman from Dioscouri]

Hey guys, I hate to bump my own threads, but I haven't heard back from
either Andrew or Louis (or anyone else "in the know"). I posted
sample code as requested, but am now left wondering if there's
something else I should do. Was the pastie a bad idea? Should I
attach a php file or two? Should I be submitting this somewhere else?

Cheers.
--
Rafael Diaz-Tushman, President & CEO
Dioscouri Design: Form and Function
www.dioscouri.com
www.twitter.com/dioscouri

On Nov 3, 8:04 am, Rafael Diaz-Tushman <rdiaztush...@dioscouri.com>
wrote:

> Thanks, Andrew.  I'm actually making a conscious effort to use the query
> builder rather than manually building the query strings.  There are only two
> small changes to be made (to insertObject & updateObject) to get the entire
> class using it.
>
> Cheers.
> --
> Rafael Diaz-Tushman, President & CEO
> Dioscouri Design: Form and Functionwww.dioscouri.comwww.twitter.com/dioscouri
>
>
>
> On Tue, Nov 3, 2009 at 5:58 AM, Andrew Eddie <mambob...@gmail.com> wrote:
>
> > One more modification you can make.  If the array contain "multiple"
> > key value pairs, check them all, eg:
>
> >                // set up an match on the named array
> >                $wheres = array();
> >                foreach ($oid as $k => $v) {
> >                        $wheres[]       = $db->NameQuote( $k
> > ).'='.$db->Quote( $v );
> >                }
>
> > then
>
> >                . ' WHERE '.implode( ' AND ', $wheres );
>
> > Just found that code snippet laying around in my code journal.
>
> > Regards,
> > Andrew Eddie

> >http://www.theartofjoomla.com- the art of becoming a Joomla developer
>
> > 2009/11/2 Rafael Diaz-Tushman from Dioscouri <rdiaztush...@dioscouri.com>:

[Andrew Eddie]

Hi Rafael

It's on my list of "don't forget to check this got into the core".
Unfortunately I've got client work to deal with for the rest of the
week but I will try to have a look for it before I leave for the
States next week.

Regards,
Andrew Eddie
http://www.theartofjoomla.com - the art of becoming a Joomla developer




2009/11/25 Rafael Diaz-Tushman from Dioscouri <rdiazt...@dioscouri.com>:

[Rafael Diaz-Tushman]

Well, Andrew, the code definitely isn't in any kind of position for being in the core yet (for instance, all the classes are named Tienda...).  From what I could tell, this was just a thread (and pastie) for discussing the potential change.  Should I send you php files that could be inserted after review?

--
Rafael Diaz-Tushman, President & CEO
Dioscouri Design: Form and Function
www.dioscouri.com
www.twitter.com/dioscouri

[Andrew Eddie]

Hi Rafael.

If you'd like to do that it would be great. If you know how to make a
diff patch, even better :)

Regards,
Andrew Eddie
http://www.theartofjoomla.com - the art of becoming a Joomla developer

2009/11/25 Rafael Diaz-Tushman <rdiazt...@dioscouri.com>:

[Rafael Diaz-Tushman]

No idea how to create a diff patch. I've never had to deal with patches in that manner -- I've always just looked at diffs in Eclipse or in a source browser. I'm just attaching the modified files and if it helps, here's a summary of the changes: 1) Added Update, Insert, and Delete capabilities to the query builder class 2) Fixed the Group method in the query builder 3) JTable now gets its properties from it's partner db table -- no need for the vars 4) Added plugin events to JTable's store, save, and delete methods that are fired if the table's _suffix is set 5) Added getter and setter for the keyNames array 6) Created tablexref.php for devs to use with xref tables Cheers.

--
Rafael Diaz-Tushman, President & CEO
Dioscouri Design: Form and Function
www.dioscouri.com
www.twitter.com/dioscouri

[Andrew Eddie]

Hi Rafael.

Those look like some awesome improvements. I'll look forward to
running them through DeltaWalker :)

I'll probably need to ask you about the xref table one though when I get to it.

[Andrew Eddie]

Ok, so I got bored tonight :)

I've committed the changes to JQuery - thankyou for those and the "having" fix.

I've committed the JTable::load modifications with a small amount of clean up.

I've also added the code that auto-populates the fields from the
database. I think I did that myself back in the Mambo days but never
got around to adding it :) So thanks for that one as well. I've
removed the manual table properties from the core tables (with one
exception) and everything still "seems" to be working. I'm leaving
the hardcoded fields in JTableAsset because they are expected and it's
an abstract table anyway.

For now, I'd like to park the triggers until all the refactoring is
done. I've done those in the models mainly because I don't want to
couple JTable too tightly to the plugin system, and sometimes you
don't want those triggers firing. Let's revisit that down the track
if we really think that's the right spot for them.

I haven't had a chance to look at your cross-referencing table yet I'm sorry.

[Rafael Diaz-Tushman]

Wow, looks great!

One thing I noticed in JTable::load -- between lines 452 and 453 you need to insert

$pk = array($keyName => $pk);

to cast the $pk as an array so your foreach loop on line 475 doesn't blow up.

I also have some ideas for refactoring the models and controllers in core components. Much of what happens in the models seems more appropriate for the controllers, but that is for another discussion, and when we run through that, we can revisit the plugin events in JTable (the biggest advantage of having them there is it makes all 3rd party devs use plugin events in their code without even thinking about it, making it much easier to extend their work...)

Thanks for all your hard work.

Cheers.

--
Rafael Diaz-Tushman, President & CEO
Dioscouri Design: Form and Function
www.dioscouri.com
www.twitter.com/dioscouri

[Andrew Eddie]

2009/11/26 Rafael Diaz-Tushman <rdiazt...@dioscouri.com>:

> Wow, looks great!
> One thing I noticed in JTable::load -- between lines 452 and 453 you need to
> insert
> $pk = array($keyName => $pk);
> to cast the $pk as an array so your foreach loop on line 475 doesn't blow
> up.

Oops, good catch. Fixed in trunk.

> I also have some ideas for refactoring the models and controllers in core
> components. Much of what happens in the models seems more appropriate for
> the controllers, but that is for another discussion, and when we run through
> that, we can revisit the plugin events in JTable (the biggest advantage of
> having them there is it makes all 3rd party devs use plugin events in their
> code without even thinking about it, making it much easier to extend their
> work...)

Yeah that sounds good. My aim at the moment is just to get all the
code consistent. It might not be perfect but it will at least allow
us to take stock and see where efficiencies can be gained - and then
it's an easy bulk change.