general problems with habtm-assocs

[Kaste]

Hi all,

seems like google has eaten my previuos mail.

simple test-setup follows:

Files habtm Tags
Tags habtm Files

tables:
files
tags
files_tags (keys: file_id, tag_id, + some other fields)

class File extends ActiveRecord
{
var $hasAndBelongsToMany = array('tags' => array ('join_table' =>
'files_tags'));
}
class Tag extends ActiveRecord
{
var $hasAndBelongsToMany = array('files' => array ('join_table' =>
'files_tags'));
}
class FileController extends ApplicationController
{
var $models = "file,tag";
function index() {
$this->Files = $this->file->find(array(1,2,3),array('include' =>
'tags'));
}
}

finally the index.tpl:
{loop Files?}
{File.full_name} <br />
<? print_r($File->tags); ?>
{end}

output:
nice filenames, empty 'tags'-arrays.

can you enlighten me?

--
..
thdz.x

[bermi]

Hi Kaste,

I haven't tried your code yet, but changing the query like this might
solve the issue.

$this->Files = $this->file->find('all', array(
'conditions' => 'id IN (1,2,3)',
'include' => 'tags'
));

FYI you can place $this->file->dbug(); in order to debug the SQL. Some
times it might be useful for solving this sort of issues.

Anyhow, I'll try your code tomorrow and will ad tests for your case, as
it doesn't work as expected.

[Kaste]

Hello bermi,

> $this->Files = $this->file->find('all', array(
> 'conditions' => 'id IN (1,2,3)',
> 'include' => 'tags'
> ));

thank you, you saved me from insanity.

>
> FYI you can place $this->file->dbug(); in order to debug the SQL. Some
> times it might be useful for solving this sort of issues.

that would have been my next question. thank you again.

next thing is, how can I access additional fields of the join table?
(created, creator...)
Should I treat them as true entities?
File hasMany Taggings
Tag hasMany Taggings
Taggings belongsTo Files,Tags

File hasMany Tags through Taggings would be nice of course.

--
..
thdz.x

[Kaste]

ok, decided that a taggings-model could be useful.
So I changed the models to:
File hasMany Taggings
Taggings belongsTo Tag

in the controller I do:

$this->Files = $this->file->find('all', array(
'conditions' => 'id IN (1,2,3)',

'include' => array('taggings')
));

and then to fetch the associated data:

foreach ($this->Files as $k => $file) {
foreach ($this->Files[$k]->taggings as $i => $tag) {
$this->Files[$k]->taggings[$i]->tag->load();
}
}

It works and somehow makes sense, but, uh, that looks ugly.

Any suggestions? "best practices".

greetings

[index.tpl
{loop Files?}
{File.id}: {File.full_name}
{loop File.taggings}
::{tagging.id},"{tagging.tag.name}"::
{end} <br />
{end}
]

[bermi]

Hi Kaste,

I've added support for adding options when using finder like you first
asked yesterday

$this->File->find(array(1,2,3), array('include' =>'tags'));

Now it works as expected.

Here is what I found the best approach to solve your problem.

If you don't use migrations, you should start versioning your DB schema
by creating the file

===================================================================
/app/installers/my_app_installer.php
===================================================================
<?php

class MyAppInstaller extends AkInstaller
{
function up_1()
{
$this->createTable('files', '
id integer max=10 auto increment primary,
name string 50'
);

$this->createTable('taggings', '
id integer max=10 auto increment primary,
file_id integer,
tag_id integer,
counter integer default 0,
updated_at datetime'
);

$this->createTable('tags', '
id integer max=10 auto increment primary,
name string 50'
);

}

function down_1()
{
$this->dropTable('files');
$this->dropTable('taggings');
$this->dropTable('tags');
}
}

?>

And then on the console running (beware of existing data prior to using
migrations)

./script/migrate my_app install

Now to the models

===================================================================
/app/models/tag.php
===================================================================

<?php

class Tag extends AkActiveRecord
{
var $habtm = array(
'files' => array(
'join_table' => 'taggings', // we customizing the join
table
'join_class_name' => 'Tagging' // and the join class
)
);

var $has_many = 'taggings'; // This is the main point for the
multiple join you wanted
}

?>

===================================================================
/app/models/file.php
===================================================================

<?php

class File extends AkActiveRecord
{
var $habtm = array(
'tags' => array(
'join_table' => 'taggings',
'join_class_name' => 'Tagging',
'include' => 'taggings'
)
);

var $has_many = 'taggings';
}

?>

===================================================================
/app/models/tagging.php
===================================================================

<?php

class Tagging extends AkActiveRecord
{
var $belongs_to = array('file', 'tag');

}

?>

// Now we create some sample records

<?php

$AkelosLogFile =& new File(array('name'=>'akelos.log'));
$AkelosLogFile->save();

$LogTag =& $AkelosLogFile->tag->create(array('name'=>'logs'));

$KasteLogFile =& new File(array('name'=>'kaste.log'));
$KasteLogFile->save();

$KasteLogFile->tag->add($LogTag);

$BermiLogFile =& new File(array('name'=>'bermi.log'));
$BermiLogFile->save();

$BermiLogFile->tag->add($LogTag);

?>

=== On your controller ===

$this->Files =& $File->find(array(1,2,3),
array('include'=>array('tags', 'taggings'))); // Just 1 SQL query to
pull all the data

=== On your view ==

{loop Files?}
{File.id}: {File.full_name}
{loop File.taggings}
::{tagging.id},"{tagging.tag.name}"::
{end} <br />
{end}

This way we have removed some ugliness from the code.

Hope you found it useful and build something interesting using the
Akelos Framework.

[Kaste]

Hello bermi,

this will take a moment till Ill get through this post.

> If you don't use migrations, you should start versioning your DB schema
> by creating the file
>
> ===================================================================
> /app/installers/my_app_installer.php
> ===================================================================

didnt know that migrations are implemented. will look into that.

> Now to the models
[...]

> === On your controller ===
>
> $this->Files =& $File->find(array(1,2,3),
> array('include'=>array('tags', 'taggings'))); // Just 1 SQL query to
> pull all the data
>

this will construct the following query:

SELECT __owner.id AS __owner_id, __owner.full_name AS __owner_full_name,
__owner.file_created AS __owner_file_created, _tags.id AS _tags_id,
_tags.name AS _tags_name, _tags.created AS _tags_created,
_taggings.file_id AS _taggings_file_id, _taggings.tag_id AS
_taggings_tag_id, _taggings.id AS _taggings_id FROM files AS __owner

LEFT OUTER JOIN taggings AS _Tagging ON __owner.id = _Tagging.file_id
LEFT OUTER JOIN tags AS _tags ON _tags.id = _Tagging.tag_id
LEFT OUTER JOIN taggings AS _taggings ON __owner.id = _taggings.file_id

WHERE __owner.id IN (1, 2, 3)

so we must get rid of the third JOIN, somehow.
We could filter via "GROUP BY _taggings_file_id,_tags_id" of course.
(btw, how do you debug the resultset (print_r-style) quickly?)

Maybe its late but the new "array-selector" breaks the where-statement.
so:
$this->Files = $this->file->find(array(1,2,3),array(
'conditions'=> 'asfd','include'=>array('tags', 'taggings'),));
^^^^^^^^
doesnt produce an error. It is ignored, you know.

> === On your view ==
>
>
> {loop Files?}
> {File.id}: {File.full_name}
> {loop File.taggings}
> ::{tagging.id},"{tagging.tag.name}"::

^^^^^^^^^^^^^^^^
I had:
Files->taggings->tags;

The triple left-join gives a flat-structure:
Files ->tags
Files ->taggings

so I cannot use a simple/nested foreach-loop.

of course Files->tags->tagging would be the most sophisticated solution.
in that case the additional fields of the tagging-object would "extend"
the tag.

so long

[bermi]

Hi Kaste,

I've updated the framework to overcome some issues related to the find
by ids, so you might might want to update your working copy.

> SELECT __owner.id AS __owner_id, __owner.full_name AS __owner_full_name,
> __owner.file_created AS __owner_file_created, _tags.id AS _tags_id,
> _tags.name AS _tags_name, _tags.created AS _tags_created,
> _taggings.file_id AS _taggings_file_id, _taggings.tag_id AS
> _taggings_tag_id, _taggings.id AS _taggings_id FROM files AS __owner
>
> LEFT OUTER JOIN taggings AS _Tagging ON __owner.id = _Tagging.file_id
> LEFT OUTER JOIN tags AS _tags ON _tags.id = _Tagging.tag_id
> LEFT OUTER JOIN taggings AS _taggings ON __owner.id = _taggings.file_id
>
> WHERE __owner.id IN (1, 2, 3)
>
> so we must get rid of the third JOIN, somehow.
> We could filter via "GROUP BY _taggings_file_id,_tags_id" of course.

What you are trying to achieve here should be carried by adding a
second-level association for eager loading "taggings" on "tags". This
is not fully ported yet into Akelos (even though is already documented)
but will be added before hitting 1.0

> (btw, how do you debug the resultset (print_r-style) quickly?)

Ak::debug($ResultSet);

Will do a print_r including only columns from the Active Records. I do
not recommend you to use it for nothing else than Active Records as it
is not aware of recursions.

> Maybe its late but the new "array-selector" breaks the where-statement.
> so:
> $this->Files = $this->file->find(array(1,2,3),array(
> 'conditions'=> 'asfd','include'=>array('tags', 'taggings'),));
> ^^^^^^^^
> doesnt produce an error. It is ignored, you know.

I've just fixed this bug, luckily only happened when passing an array
of id's

> The triple left-join gives a flat-structure:
> Files ->tags
> Files ->taggings
>
> so I cannot use a simple/nested foreach-loop.
>
> of course Files->tags->tagging would be the most sophisticated solution.
> in that case the additional fields of the tagging-object would "extend"
> the tag.

You can do:

$Files =& $File->find('all', array(
'include'=>array('tags')
));

foreach ($Files as $File){
Ak::debug($File);
foreach ($File->tags as $Tag){
Ak::debug($Tag);
$Tag->tagging->load();
foreach ($Tag->taggings as $Tagging){
Ak::debug($Tagging);
}
}
}

This would be the simplest way right now (until we implement
second-level associations), but it is far too SQL expensive as every
"load()" hits the database.

You might want to add this function into your File model. This way
you'll get the same result using just 2 SQL queries. It's ugly on the
Model, but much more efficient (and elegant in the controller) than the
loading one-by-one technique.

===================================================================
/app/models/file.php
===================================================================

<?php

class File extends AkActiveRecord
{
var $habtm = array(
'tags' => array(
'join_table' => 'taggings',
'join_class_name' => 'Tagging',

)
);

var $has_many = 'taggings';

function &findWithTagsAndTagging($files = array(), $options =
array())
{
// Just a quick solution (there is still room for improvement
on this)

$File =& new File();
$Files =& $File->find('all',
array_merge(array('include'=>array('tags')), $options));

$tag_ids = array();
foreach ($Files as $File){
foreach ($File->tags as $Tag){
$tag_ids[] = $Tag->getId();
}
}

$Tag =& new Tag();
$Tags =& $Tag->find($tag_ids, array('include'=>'taggings'));

foreach (array_keys($Files) as $k){
foreach (array_keys($Files[$k]->tags) as $m){
foreach (array_keys($Tags) as $n){
if($Tags[$n]->id == $Files[$k]->tags[$m]->id){
$Files[$k]->tags[$m]->taggings =&
$Tags[$n]->taggings;
}
}
}
}

return $Files;
}

}

?>

[bermi]

My previous example was just for PHP5 as on PHP4 iterators create
copies not references, so in case you are on PHP4 you'll need something
like this:

<?php

foreach (array_keys($Files) as $k){
$File =& $Files[$k];
foreach (array_keys($File->taggings) as $l){
$Tagging =& $File->taggings[$l];
$Tagging->tag->load();
}
}

?>

[Kaste]

Hi bermi,
thank you for your effort.

>>
>> so we must get rid of the third JOIN, somehow.
>> We could filter via "GROUP BY _taggings_file_id,_tags_id" of course.
>
> What you are trying to achieve here should be carried by adding a
> second-level association for eager loading "taggings" on "tags". This
> is not fully ported yet into Akelos (even though is already documented)
> but will be added before hitting 1.0
>
>> (btw, how do you debug the resultset (print_r-style) quickly?)
> Ak::debug($ResultSet);

still a bit too much information IMHO. (I only want to see the
data-*structure*/a tree.)
anyway thank you.

> You can do:
>
> $Files =& $File->find('all', array(
> 'include'=>array('tags')
> ));
>
> foreach ($Files as $File){
> Ak::debug($File);
> foreach ($File->tags as $Tag){
> Ak::debug($Tag);
> $Tag->tagging->load();
> foreach ($Tag->taggings as $Tagging){
> Ak::debug($Tagging);
> }
> }
> }

this is somehow similiar to my solution with tags/taggings swapped.

next solution isnt clear to me

> function &findWithTagsAndTagging($files = array(), $options =
> array())
> {
>

> $File =& new File();
> $Files =& $File->find('all',
> array_merge(array('include'=>array('tags')), $options));
>
> $tag_ids = array();
> foreach ($Files as $File){
> foreach ($File->tags as $Tag){
> $tag_ids[] = $Tag->getId();
> }
> }
>
> $Tag =& new Tag();
> $Tags =& $Tag->find($tag_ids, array('include'=>'taggings'));

this query means: Give me all tagged files (= taggings) which have the
same tags as my previously selected files in a raw list.
1. this is inefficient, 'cause this must be a huge list. (and we simply
dont want this rows.)
2. we create Tag-Objects we already have.
file1 ->tag1,2,3 ->tagging1,2,3
file2 ->tag2,4,5 ->tagging4,5,6
=> create 3 tag-objects + 3 tagging-objects

>
> foreach (array_keys($Files) as $k){
> foreach (array_keys($Files[$k]->tags) as $m){
> foreach (array_keys($Tags) as $n){
> if($Tags[$n]->id == $Files[$k]->tags[$m]->id){
> $Files[$k]->tags[$m]->taggings =&
> $Tags[$n]->taggings;

Tags[n][taggings] is therefore the comlete list of "similiar tagged" files.
But we only want the extra-fields of one tagging. We want: a file has
three tags associated, now kinda describe each association: Creation-date,
Owner, "weight", position, Deletion-date...

Dont know if we can call this a "solution":

function &findWithTagsAndTagging($ids = 'all', $options = array()) {

$File =& new File();
$Files =&

$File->find($ids,array_merge(array('include'=>array('tags')), $options));

$file_ids = array();
foreach ($Files as $File) {
$file_ids[] = $File->getId();
}

$Tagging =& new Tagging();
$Taggings =& $Tagging->find('all', array(
'conditions' => 'file_id IN ('. implode(",",$file_ids)
.')'));

foreach (array_keys($Files) as $k){
foreach (array_keys($Files[$k]->tags) as $m){

foreach (array_keys($Taggings) as $n){
if($Taggings[$n]->file_id == $Files[$k]->id &&
$Files[$k]->tags[$m]->id == $Taggings[$n]->tag_id){ //ouch
$Files[$k]->tags[$m]->taggings = $Taggings[$n];
}
}
}
}
return $Files;
}

but I may be missing something
tbc...

[Kaste]

> We could filter via "GROUP BY _taggings_file_id,_tags_id" of course.

"of course" we cant. we can only go to bed when its time to.

[Kaste]

ok, we have these two methods

1. retrieve the missing data via ->load().
2. build 2 queries and construct a object-tree via multi-"you dont wanna
write this kind of code very often"-foreachs.

doing some benchmarks...
ab -c 1 -t 60 http://.....

fetching 20 files. id IN (1,..,20)
method1: 3929ms/req ~3.0 for the fetching (not SQL, building the real
model)
method2: 3677ms/req ~2.7

fetching 100 files
m1: 22835ms/req ~19.0 only fetching
m2: 20082ms/req ~15.7

so method2 is better than method1.
both are too slow.

[bermi]

I think you should try to build up a custom SQL query and then run it
using the PHP ADOdb driver like:

$db =& Ak::db(); // see http://phplens.com/adodb/

That might be a faster approach for getting the information you want.