DataMapper ORM v1.8.1

[eluser]WanWizard[/eluser]
It doesn't create any temp tables, the table names you see in the explain output are the table aliases in the query.

In actual fact, explain shows that the query is quite optimal, the correct indexes are used everywhere, no table scans are involved. Of course, a query like this will have lots of intermediate results, which need to be stored somewhere. Depending on your database server, that might be in a temp table on disk, slowing things down considerably. You'll have to check your MySQL statistics to see if this is the case.

As you're running a query on a fund id, can't you reverse the query? Start with the fund record, and fetch the related items?

[eluser]Anestetikas[/eluser]
[quote author="WanWizard" date="1308749131"]It doesn't create any temp tables, the table names you see in the explain output are the table aliases in the query.

In actual fact, explain shows that the query is quite optimal, the correct indexes are used everywhere, no table scans are involved. Of course, a query like this will have lots of intermediate results, which need to be stored somewhere. Depending on your database server, that might be in a temp table on disk, slowing things down considerably. You'll have to check your MySQL statistics to see if this is the case.

As you're running a query on a fund id, can't you reverse the query? Start with the fund record, and fetch the related items?[/quote]

Well its ondisk temp table. And it generates tens of millions of rows, and its still a very small document table, it should grow to like 500x the size of files table.

I use iterate to get languages for each object. And if I used to include related -< fields, it would be 1 fund -< ~5-6 inventories -< ~ 50-5k files(ea inventory) -< ~ 1-700 documents (ea file). So I think including related objects, would make a huge overhead.

If understand correctly, I would do

Code:

foreach($fund->inventory->file->document as $document)

It would go through ~ 4 million $document.

Or I dont get the include related thingy going -< relations?

Atm my code looks like:

Code:

//get related languages
                $language_ids = array();
                $kalbos = new Language;

                //from inventories
                $kalbos->select('id');
                $kalbos->where_related('inventory/fund', 'id', $fondas->id)->group_by('id')->get_iterated();
                $language_ids = array_merge($language_ids, $kalbos->all_to_array('id'));
                $kalbos->clear();

                //from files
                $kalbos->select('id');
                $kalbos->where_related('file/inventory/fund', 'id', $fondas->id)->group_by('id')->get_iterated();
                $language_ids = array_merge($language_ids, $kalbos->all_to_array('id'));
                $kalbos->clear();

                //from documents
                $kalbos->select('id');
                $kalbos->where_related('document/file/inventory/fund', 'id', $fondas->id)->group_by('id')->get_iterated();
                $language_ids = array_merge($language_ids, $kalbos->all_to_array('id'));
                $kalbos->clear();

                //select needed values from associative array to standard array for select
                $langIDS = array();
                foreach ($language_ids as $single) {
                    $langIDS[] = $single['id'];
                }

                //get all related languages
                if (!empty($langIDS)) {
                    $kalbos->select('description')->where_in('id', $langIDS)->get_iterated();
                }

                $this->data['kalbos'] = $kalbos;

[eluser]WanWizard[/eluser]
I'm still trying to understand your database structure, query, and question.

Issue is that Datamapper, as an ORM, is built to work with objects and collections of objects. It is not made to be used as a complete replacement for any query you can come up with.

Sometimes is simply better to use a standard AR query if that provides a quicker and better result. You can do that from a method in your language model:

Code:

function get_by_fund_id($fund_id)
{
    $query = $this->db->query('YOUR QUERY HERE USING $fund_id');

    // Convert the query result into DataMapper objects
    if($query)
    {
        $this->_process_query($query);
    }

    // For method chaining
    return $this;
}

And use it like

Code:

$l = new Language();
$l->get_by_fund_id(1);
foreach ( $l as $language )
{
    // do something with the result
}

[eluser]bEz[/eluser]
...and that's why you gotta love CodeIgniter

The "Pow-Waa (power)" to enhance the core through a solid library like DM, yet resourceful enough to resort to the very core functionality when in a bind.

[eluser]WanWizard[/eluser]
Even better: Datamapper uses CodeIgniter's DB library internally as well, and it's record validation methods re-use the validation library.

[eluser]RS71[/eluser]
Is there a way to automatically join a related when getting something?

Lets say I have a Property model and an Address model - is there a way to automatically do a include_related? So that instead of doing two queries, it does one with a join whenever I do $p->get() ?

[eluser]WanWizard[/eluser]
No, there isn't.

But you can simply add a method to your property model:

Code:

public function get_with_address()
{
    return $this->include_related(...)->get();
}

and use

Code:

$p->where(...)->get_with_address();

in your controller.

[eluser]heldrida[/eluser]
Hi,
I’ve previously posted this at stackoverflow (http://stackoverflow.com/questions/64709...ion-for-it), but it’s probably easier to find help here.

Q: How to set models for Adjacency Lists, when using Datamapper ORM. Anyone experienced ? For example, Table Category ( id, name, has_parent_category_id ). Thanks for looking!

Example table,

Tbl Category

id | name | has_parent_category_id
1 | catA | 0
2 | catB | 0
3 | catC | 1
4 | catD | 2
5 | catE | 3

The docs are in http://datamapper.wanwizard.eu/pages/adv...tions.html and I found the following “Self Relationships”. I’m not sure if this is the right way! I’m testing it.

If anyone is experienced with Adjacency lists, codeigniters Datamapper ORM and would mind leaving one practical example on how to do this, I would appreciate it!

Thanks for your time!

[eluser]WanWizard[/eluser]
If you're looking to store tree structures, you might want to have a look at the nestedsets extension, included in the Datamapper distribution. See http://datamapper.wanwizard.eu/pages/ext...dsets.html for more information.

If you insist on using Adjacency lists:

Code:

class Mylist extends DataMapper {
    $has_many = array(
        'parent' => array(
            'class' => 'mylist',
            'other_field' => 'mylist',
        ),
        'mylist' => array(
            'other_field' => 'parent',
        )
    );
}

You'll have to write the methods to navigate through the list yourself, where the nestedsets extension includes all methods to deal with parents, children and siblings...

[eluser]heldrida[/eluser]
Thanks a lot for your suport!

I've got a project that already has it's own tables and relationships. The names and relations are in the form datamapper asks to be, but I'm trying to figure out, what if the FK has a unusual name. Let's use the user and group example:

tblUSERS

id | username | from_group
1 | john | 2
2 | peter | 2
3 | mary | 1

tblGROUPS

id | name
1 | administrator
2 | member

Has you see, tblUSERS should have the FK as "group_id", but in this case is "from_group". I dont know if I can do this, but after trying testing by following (http://datamapper.wanwizard.eu/pages/adv...tions.html), I couldnt figure it out!



You want to use alternative name for a foreign key column name. Say want to use writer_id instead of author_id.

* In the Author class, use 'writer' for the join_self_as value.

* In the Book class, use 'writer' for the join_other_as value.

(*) Its not supported right ?


I've checked the source code, confirming that "_id" is hardcoded, for example:

$other_column = $related_properties['join_other_as'] . '_id';

So, its confirmed! This can be a nice feature on the next versions.

Sorry about taking your time,

Any kind of help is appreciated!

-------- edit

Ooops! found the answer here

http://ellislab.com/forums/viewthread/188751/

Thanks