DataMapper ORM v1.8.1

[eluser]tarciozemel[/eluser]
Limit with range dynamically

Hi, people!

If I use

Code:

$limit = 9;
$foo->limit($limit)->get_iterated();

Everything runs fine and the generated query is OK. But, if I use

Code:

$limit = '9,9';
$foo->limit($limit)->get_iterated();

The generated query not contains the LIMIT part!

Any ideas about how about I can pass range values in limit with DM?

Regards!

[eluser]tarciozemel[/eluser]
OK, I already see my mistake: "9,9" it's just 1 param and the function needs 2.

If WanWizard judging this is good, the limit function can be changed to:

Code:

/**
* Limit
*
* Sets the LIMIT portion of the query.
*
* @param integer $limit Limit the number of results.
* @param integer|NULL $offset Offset the results when limiting.
* @return DataMapper Returns self for method chaining.
*/
public function limit($value, $offset = '')
{
    if ( ! strstr($value, ','))
    {
        $this->db->limit($value, $offset);
    }
    else
    {
        $value = explode(',', $value);
        $this->db->limit($value[0], trim($value[1]));
    }

    // For method chaining
    return $this;
}

Regards!

[eluser]smi23[/eluser]
Hi everyone!
I have a little problem with auto populating:

My global config variable

Code:

$config['auto_populate_has_many'] = FALSE;

I need to change this for one model (e.g. Country) to TRUE.
So I add this line to the Country model:

Code:

var $auto_populate_has_many = TRUE;

But, when I get countries, there is no has many objects (e.g. $countries->user->id) in the result. ($countries->auto_populate_has_many is still FALSE).
Strange, but if global variable is TRUE everything is OK.

Big thanks for any response.

[eluser]WanWizard[/eluser]
Which version are you using?

You're posting in the 1.8.1 thread which is no longer the current version. There was an issue with overriding global config values in the model which has been fixed.

So I suggest you upgrade to the latest version from Bitbucket first and see if that fixes it.

[eluser]John Murowaniecki[/eluser]
Gentlemans,

I know that is my fault but I cannot see any solution more - yes, before went here I've searched almost all this topic, the user guide, the documention and other foruns, so I'm very tired and blind or this way is "correct".

Well, let me explain the whole picture:
I have a model that has many votes and one profile information. There is the relations:

Code:

class Cadastro extends DataMapper
{
var $model = 'cadastro';
var $table = 'new_cadastros';
#

var $has_one = array();
var $has_many = array('meme');
#

#..
}
#

class Meme extends DataMapper
{
var $model = 'meme';
var $table = 'new_memes';stalls
#

var $has_one = array('cadastro');
var $has_many = array('voto');
#

#..
}
#

class Voto extends DataMapper
{
var $model = 'voto';
var $table = 'new_votos';
#

var $has_one = array('meme');
var $has_many = array();
#

#..
}
#

..Basicly everything works fine - including this problematic query - with few registries.. But with many (at least 20k of new_memes and 800k of new_votes) it freezes.

Code:

$memes = new Meme();

$count = $memes->where('status','1')->count();
$memes ->include_related_count('voto')
->include_related('cadastro', array('id', 'nome'))
->limit(10)
->order_by('voto_count DESC')
->get_by_status(1);

Everything is ok, except for the sloooooOooo..oowest query generated:

Code:

SELECT `new_memes`.*

,( SELECT COUNT(*) AS count
FROM (`new_votos`)
LEFT OUTER JOIN `new_memes` `new_memes_subquery`
  ON `new_memes_subquery`.`id` = `new_votos`.`meme_id`
WHERE `new_memes_subquery`.id = `new_memes`.`id`) AS voto_count
,  `new_cadastros`.`nome` AS cadastro_nome

FROM (`new_memes`)

LEFT OUTER JOIN `new_cadastros` new_cadastros
ON `new_cadastros`.`id` = `new_memes`.`cadastro_id`

WHERE `new_memes`.`status` = 1
ORDER BY `voto_count` DESC
LIMIT 10

Well.. If we cut off the LEFT OUTHER JOIN from the subquery we made this more than 20x faster (at least doesn't brick the site). So this is the optimized query:

Code:

SELECT `new_memes`.*

,( SELECT COUNT(*) AS count
FROM (`new_votos`)
WHERE `new_votos`.meme_id = `new_memes`.`id`) AS voto_count
,  `new_cadastros`.`nome` AS cadastro_nome

FROM (`new_memes`)

LEFT OUTER JOIN `new_cadastros` new_cadastros
ON `new_cadastros`.`id` = `new_memes`.`cadastro_id`

WHERE `new_memes`.`status` = 1
ORDER BY `voto_count` DESC
LIMIT 10

..The problem is: I can't realise the query $writed->like_this()->way(1). And I need a faster query (if possible). Owyeah: I'm already using the CI cache and sorry, but I didn't see any way to resolve this without asking you. So how can I resolve this? There's an easy way?

Should I create a view for this? Or manage cached queries? Or anything else?

Thanks in advance.

[eluser]WanWizard[/eluser]
Instead of using include_related_count(), create the subquery manually on a separate object, and add that to the main query.

See http://datamapper.wanwizard.eu/pages/subqueries.html on how to create and use subqueries.

[eluser]smi23[/eluser]
[quote author="WanWizard" date="1333990389"]Which version are you using?

You're posting in the 1.8.1 thread which is no longer the current version. There was an issue with overriding global config values in the model which has been fixed.

So I suggest you upgrade to the latest version from Bitbucket first and see if that fixes it.[/quote]
Thanks for the reply.

Sorry, missed the topic name. My version is 1.8.2 (taken from https://bitbucket.org/wanwizard/datamapp..._1.8.2.zip ).
I have also noticed that if I comment this line in a config file

Code:

$config['auto_populate_has_many'] = FALSE;

auto population will work.

[eluser]WanWizard[/eluser]
That is not the latest version, the bugfix for this was after that (tagged 1.8.2.1).

Change line 485 to read

Code:

if ( ! property_exists($this, $config_key))

it should only use the global setting if no local setting is provided.

[eluser]smi23[/eluser]
[quote author="WanWizard" date="1334051260"]That is not the latest version, the bugfix for this was after that (tagged 1.8.2.1).

Change line 485 to read

Code:

if ( ! property_exists($this, $config_key))

it should only use the global setting if no local setting is provided.[/quote]
Yes, that was a problem.
Thanks for help, and BIG thanks for DM!

[eluser]John Murowaniecki[/eluser]
[quote author="WanWizard" date="1334008176"]Instead of using include_related_count(), create the subquery manually on a separate object, and add that to the main query.

See http://datamapper.wanwizard.eu/pages/subqueries.html on how to create and use subqueries.[/quote]

..Well, may I say that now I know how to optimize subqueries ^__^ ..Unfortunately this did not help me as expected (that is the great problem of creating expectations about something), but I do not see optimally perform this procedure (yes, I create a view to work with it and there was not much faster).

But here's the solution that we chose to use: keep making records usually vote, but every time someone votes I update the record of voting with the counting of votes..

Dudes, that made ​​the queries rather rapidly slipping in butter (:

I thank heartily all the help I have received here. Seriously. But I would say that, sometimes, the best solution to a given problem is too ridiculous to even be considered.

I'm sorry if my solution is too stupid for you. (;