QB and "the parameters problem"
-
Eero af Heurlin
QB and "the parameters problem"
Mon June 22 2009 12:15:10 UTC---<snip>---
Piotr Pokora
2:59
parameters problem ?
Eero af Heurlin
3:00
i mean that if I add parameter.domain=something and parameter.value=else
it means I want objects that have parameters where in single parameter
domain and value are what is set
instead of the "current" where if there are two parameters where other
has the domain and other the value it's a match
Piotr Pokora
3:01
no, it's self join problem
Eero af Heurlin
3:02
if it cannot be sensibly solved I suggest we remove the possibility to
query parameters like that
but that's another discussion...
---<snip>---
Moving this discussion to the dev list.
_______________________________________________
dev mailing list
dev@lists.midgard-project.org
http://lists.midgard-project.org/mailman/listinfo/dev -
-
Re: [midgard-dev] QB and "the parameters problem"
Mon June 22 2009 12:50:07 UTCEero af Heurlin schrieb:
> ---<snip>---
> Piotr Pokora
> 2:59
> parameters problem ?
>
> Eero af Heurlin
> 3:00
> i mean that if I add parameter.domain=something and parameter.value=else
> it means I want objects that have parameters where in single parameter
> domain and value are what is set
> instead of the "current" where if there are two parameters where other
> has the domain and other the value it's a match
>
> Piotr Pokora
> 3:01
> no, it's self join problem
>
> Eero af Heurlin
> 3:02
> if it cannot be sensibly solved I suggest we remove the possibility to
> query parameters like that
> but that's another discussion...
> ---<snip>---
>
> Moving this discussion to the dev list.
I don't know how this works internally, but API-wise, couldn't you just
add a new function
add_parameter_constraint($domain, $name, $op, $value)
that creates the appropriate SQL?
F.x.:
$qb = midcom_db_article::new_query_builder();
$qb->add_parameter_constraint('midcom.helper.datamanager2', 'schema',
'=', 'article')
would do something like
SELECT * from article, record_extension where
record_extension.domain='...' AND record_extension.name='...' AND
record_extension.value='...' AND record_extension.parent_guid = article.guid
> _______________________________________________
> dev mailing list
> dev@lists.midgard-project.org
> http://lists.midgard-project.org/mailman/listinfo/dev
_______________________________________________
dev mailing list
dev@lists.midgard-project.org
http://lists.midgard-project.org/mailman/listinfo/dev -
-
Re: [midgard-dev] QB and "the parameters problem"
Mon June 22 2009 13:10:08 UTCAndreas Flack writes:
Hi!
> add_parameter_constraint($domain, $name, $op, $value)
This is exactly what we do with reserved 'parameter' constraint name.
> would do something like
>
> SELECT * from article, record_extension where
> record_extension.domain='...' AND record_extension.name='...' AND
> record_extension.value='...' AND record_extension.parent_guid = article.guid
It'll work only when you query against one parameter record.
If there are more parameters constraints and thus, more parameter
records expected,
core should join parameter tables:
SELECT * from article, record_extension, record_extension as
record_extensionA
WHERE record_extension.domain='...' AND record_extension.nameA='...' AND
Piotras
_______________________________________________
dev mailing list
dev@lists.midgard-project.org
http://lists.midgard-project.org/mailman/listinfo/dev -
-
Re: [midgard-dev] QB and "the parameters problem"
Mon June 22 2009 13:30:11 UTCPiotr Pokora schrieb:
> Andreas Flack writes:
>
> Hi!
>
>> add_parameter_constraint($domain, $name, $op, $value)
>
> This is exactly what we do with reserved 'parameter' constraint name.
I'm confused: I thought the problem with
$qb->add_constraint('parameter.domain', '=', 'foo');
$qb->add_constraint('parameter.value', '=', 'bar');
is exactly that they are not applied to the same parameter, and that
begin_group('AND') cannot be used to group the two together, or at least
that's how I understood these:
http://www.midgard-project.org/documentation/midgardquerybuilder-complex-constraints/
http://www.mail-archive.com/user@lists.midgard-project.org/msg00410.html
>
>> would do something like
>>
>> SELECT * from article, record_extension where
>> record_extension.domain='...' AND record_extension.name='...' AND
>> record_extension.value='...' AND record_extension.parent_guid = article.guid
>
> It'll work only when you query against one parameter record.
> If there are more parameters constraints and thus, more parameter
> records expected,
> core should join parameter tables:
>
> SELECT * from article, record_extension, record_extension as
> record_extensionA
> WHERE record_extension.domain='...' AND record_extension.nameA='...' AND
Yeah well, I'm not good with SQL (which is why I use midgard in the
first place :-), my use case would just be something like "Find all
articles that have DM2 schema set to 'article' and have my special site
parameter 'visible_to_users' set to true", so I want QB to return all
articles that have these two parameters:
P1: domain "midcom.helper.datamanager2"; name "schema"; value "article"
P2: domain "mysite"; name "visible_to_users"; value "true"
how exactly this works best in SQL I'll leave to the experts, but I
would like to be able to write this in PHP with only one QB
instantiation and ideally with only two lines of constraints.
Bye,
Andreas
>
> Piotras
> _______________________________________________
> dev mailing list
> dev@lists.midgard-project.org
> http://lists.midgard-project.org/mailman/listinfo/dev
_______________________________________________
dev mailing list
dev@lists.midgard-project.org
http://lists.midgard-project.org/mailman/listinfo/dev -
-
Re: [midgard-dev] QB and "the parameters problem"
Mon June 22 2009 13:45:06 UTCAndreas Flack schrieb:
>
> Piotr Pokora schrieb:
>> Andreas Flack writes:
>>
>> Hi!
>>
>>> add_parameter_constraint($domain, $name, $op, $value)
>> This is exactly what we do with reserved 'parameter' constraint name.
>
> I'm confused: I thought the problem with
>
> $qb->add_constraint('parameter.domain', '=', 'foo');
> $qb->add_constraint('parameter.value', '=', 'bar');
>
> is exactly that they are not applied to the same parameter, and that
> begin_group('AND') cannot be used to group the two together
P.S.: Also, it would be very illogical if the two constraints listed
above would only work if both match on the same parameter, because then
something like
$qb->add_constraint('parameter.value', '=', 'foo');
$qb->add_constraint('parameter.value', '=', 'bar');
would always return zero results, which can't be right either.
Bye,
Andreas
_______________________________________________
dev mailing list
dev@lists.midgard-project.org
http://lists.midgard-project.org/mailman/listinfo/dev -
-
Re: [midgard-dev] QB and "the parameters problem"
Mon June 22 2009 14:45:09 UTCAndreas Flack writes:
Hi!
>> I'm confused: I thought the problem with
>>
>> $qb->add_constraint('parameter.domain', '=', 'foo');
>> $qb->add_constraint('parameter.value', '=', 'bar');
>>
>> is exactly that they are not applied to the same parameter, and that
>> begin_group('AND') cannot be used to group the two together
Just tell me how QB could know they are not applied to the same
parameter? :)
Real life example:
$qb = new midgard_query_builder("midgard_person");
$qb->add_constraint("parameter.domain", "=", "midcom");
$qb->add_constraint("parameter.name", "=", "first_login");
$qb->add_constraint("parameter.value", "=", "1240836123");
It returns one person object.
> P.S.: Also, it would be very illogical if the two constraints listed
> above would only work if both match on the same parameter, because then
> something like
>
>
> $qb->add_constraint('parameter.value', '=', 'foo');
> $qb->add_constraint('parameter.value', '=', 'bar');
>
> would always return zero results, which can't be right either.
But this is *you* who knows it can't be right. QB just adds constraints
and create
proper SQL query. It's not DB/SQL/resultset validator.
Latter example *can* work only ( and only ) if QB is able to join more
than one
parameter tables. But to make this, QB should know *when* join should be
exactly made.
Piotras
_______________________________________________
dev mailing list
dev@lists.midgard-project.org
http://lists.midgard-project.org/mailman/listinfo/dev -
-
Re: [midgard-dev] QB and "the parameters problem"
Mon June 22 2009 15:30:07 UTCPiotr Pokora schrieb:
> Andreas Flack writes:
>
> Hi!
>
>>> I'm confused: I thought the problem with
>>>
>>> $qb->add_constraint('parameter.domain', '=', 'foo');
>>> $qb->add_constraint('parameter.value', '=', 'bar');
>>>
>>> is exactly that they are not applied to the same parameter, and that
>>> begin_group('AND') cannot be used to group the two together
>
> Just tell me how QB could know they are not applied to the same
> parameter? :)
>
> Real life example:
>
> $qb = new midgard_query_builder("midgard_person");
> $qb->add_constraint("parameter.domain", "=", "midcom");
> $qb->add_constraint("parameter.name", "=", "first_login");
> $qb->add_constraint("parameter.value", "=", "1240836123");
>
> It returns one person object.
But not necessarily one where a parameter 'midcom', 'first_login',
'1240836123' exists, it could also be a person where midcom.first_login
is "1222222222" and midcom.last_login is "1240836123". Or am I missing
something?
>
>> P.S.: Also, it would be very illogical if the two constraints listed
>> above would only work if both match on the same parameter, because then
>> something like
>>
>>
>> $qb->add_constraint('parameter.value', '=', 'foo');
>> $qb->add_constraint('parameter.value', '=', 'bar');
>>
>> would always return zero results, which can't be right either.
>
> But this is *you* who knows it can't be right. QB just adds constraints
> and create
> proper SQL query. It's not DB/SQL/resultset validator.
OK, maybe I'm not really getting this, but I always thought the
semantics of parameter constraints would be something like "Find all
objects where any of the parameters matches my constraint", which would
by extension mean that multiple constraints would be "where parameters
exist that fulfill all my constraints (and there's no way to tell which
parameter fulfills which constraint)". Or is it rather "where exactly
one parameter exists that fulfills all my constraints"?
Anyhow, IMHO this goes to show that the parameter notation is quite
unintuitive. It looks like the metadata/linked field notation, but
actually is something else entirely, because you're not working with a
1:1 relation, but a 1:m one:
$qb->add_constraint("metadata.created", ">", "1240836123");
$qb->add_constraint("metadata.created", "<", "1250836123");
and
$qb->add_constraint("parameter.value", ">", "1240836123");
$qb->add_constraint("parameter.value", "<", "1250836123");
are inherently different, and trying to force them to behave the same
will lead to much confusion. Hence my proposal to do parameter
constraints with a separate function, and if it were up to me, I'd
remove the parameter notation entirely, because querying two parameters
would be just painful even if the resulting SQL would join correctly:
$qb->add_constraint("metadata.created", ">", "1240836123");
$qb->add_constraint("metadata.published", "<", "1250836123");
vs.
$qb->begin_group('AND');
$qb->add_constraint("parameter.domain", "=", "midcom");
$qb->add_constraint("parameter.name", "=", "first_login");
$qb->add_constraint("parameter.value", ">", "1240836123");
$qb->end_group();
$qb->begin_group('AND');
$qb->add_constraint("parameter.domain", "=", "midcom");
$qb->add_constraint("parameter.name", "=", "last_login");
$qb->add_constraint("parameter.value", "<", "1250836123");
$qb->end_group();
>
> Latter example *can* work only ( and only ) if QB is able to join more
> than one
> parameter tables. But to make this, QB should know *when* join should be
> exactly made.
but wouldn't that be easier for QB to decide if you had a dedicated
add_parameter_constraint(domain, name, op, value) function (especially
since we agree that in real life, domain, name and value constraints are
almost always used together)?
Bye,
Andreas
>
> Piotras
> _______________________________________________
> dev mailing list
> dev@lists.midgard-project.org
> http://lists.midgard-project.org/mailman/listinfo/dev
_______________________________________________
dev mailing list
dev@lists.midgard-project.org
http://lists.midgard-project.org/mailman/listinfo/dev -
-
Re: [midgard-dev] QB and "the parameters problem"
Mon June 22 2009 15:45:06 UTCAndreas Flack writes:
Hi!
> OK, maybe I'm not really getting this, but I always thought the
> semantics of parameter constraints would be something like "Find all
> objects where any of the parameters matches my constraint",
Yes, would be. But would do some voodoo magic to guess what to do at all.
> Anyhow, IMHO this goes to show that the parameter notation is quite
> unintuitive. It looks like the metadata/linked field notation, but
> actually is something else entirely, because you're not working with a
> 1:1 relation, but a 1:m one:
I bet 1:m is reproducable with linked properties too.
> are inherently different, and trying to force them to behave the same
> will lead to much confusion. Hence my proposal to do parameter
> constraints with a separate function, and if it were up to me, I'd
> remove the parameter notation entirely, because querying two parameters
> would be just painful even if the resulting SQL would join correctly:
Still, how would you like to tell QB it must do some join(s) when using
parameter constraint method?
Piotras
_______________________________________________
dev mailing list
dev@lists.midgard-project.org
http://lists.midgard-project.org/mailman/listinfo/dev -
-
Re: [midgard-dev] QB and "the parameters problem"
Mon June 22 2009 16:10:05 UTCPiotr Pokora schrieb:
> Andreas Flack writes:
>
> Hi!
>
>> OK, maybe I'm not really getting this, but I always thought the
>> semantics of parameter constraints would be something like "Find all
>> objects where any of the parameters matches my constraint",
>
> Yes, would be. But would do some voodoo magic to guess what to do at all.
>
>> Anyhow, IMHO this goes to show that the parameter notation is quite
>> unintuitive. It looks like the metadata/linked field notation, but
>> actually is something else entirely, because you're not working with a
>> 1:1 relation, but a 1:m one:
>
> I bet 1:m is reproducable with linked properties too.
Hm. I don't see how, but of course that doesn't mean its not possible :-)
What I was trying to say is that when you have something like
'customer.official' or 'metadata.created' customer or metadata implictly
refer to exactly one object that is linked to an entry of the table
you're operating on (OK, in the case of metadata it's actually referring
to prefixed fields in the same table, but that's beside the point).
'parameter.value' is misleading, because there's not exactly one
parameter per entry, but potentially a whole lot of them (so it should
more accurately be called 'parameters.value', which also makes the
problem a bit more visible).
>
>> are inherently different, and trying to force them to behave the same
>> will lead to much confusion. Hence my proposal to do parameter
>> constraints with a separate function, and if it were up to me, I'd
>> remove the parameter notation entirely, because querying two parameters
>> would be just painful even if the resulting SQL would join correctly:
>
> Still, how would you like to tell QB it must do some join(s) when using
> parameter constraint method?
>
Well, my naive idea was to do one join per parameter_constraint call,
and maybe create an exception for AND groups where the individual
constraints refer to the same domain/name (but OTOH the combination of
these two is unique for each parent_guid, so I don't know if that's
really necessary)
Bye,
Andreas
> Piotras
>
> _______________________________________________
> dev mailing list
> dev@lists.midgard-project.org
> http://lists.midgard-project.org/mailman/listinfo/dev
_______________________________________________
dev mailing list
dev@lists.midgard-project.org
http://lists.midgard-project.org/mailman/listinfo/dev -
-
Re: [midgard-dev] QB and "the parameters problem"
Mon June 22 2009 16:50:08 UTCAndreas Flack writes:
Hi!
> Well, my naive idea was to do one join per parameter_constraint call,
> and maybe create an exception for AND groups where the individual
> constraints refer to the same domain/name (but OTOH the combination of
> these two is unique for each parent_guid, so I don't know if that's
> really necessary)
I made few example queries:
mysql> select id, sitegroup, domain, parent_guid from record_extension;
+----+-----------+----------------------------------------+---------------------------------------------+
| id | sitegroup | domain | parent_guid
|
+----+-----------+----------------------------------------+---------------------------------------------+
| 1 | 1 | midgard |
1de33289ca860ce332811dead78535bd22a67846784 |
| 2 | 1 | midcom |
1de33289a10ef52332811dead78535bd22a67846784 |
| 3 | 1 | midcom |
1de33289a10ef52332811dead78535bd22a67846784 |
mysql> SELECT person.id, username FROM person INNER JOIN
record_extension ON record_extension.parent_guid=person.guid WHERE
username<>'' AND record_extension.domain='midcom' AND
record_extension.domain='midgard';
Empty set (0.00 sec)
I would expect to at least one record.
mysql> SELECT person.id, username FROM person INNER JOIN
record_extension ON record_extension.parent_guid=person.guid WHERE
username<>'' AND record_extension.domain='midcom' OR
record_extension.domain='midgard';
+----+----------+
| id | username |
+----+----------+
| 2 | admin |
| 2 | admin |
+----+----------+
2 rows in set (0.00 sec)
I would expect to at least one record but not duplicated.
mysql> SELECT person.id, username FROM person INNER JOIN
record_extension ON record_extension.parent_guid=person.guid INNER JOIN
record_extension A ON person.guid=A.parent_guid WHERE username<>'' AND
record_extension.domain='midcom' OR record_extension.domain='midgard';
+----+----------+
| id | username |
+----+----------+
| 2 | admin |
| 2 | admin |
| 2 | admin |
| 2 | admin |
+----+----------+
4 rows in set (0.00 sec)
This is join made per constraint.
Suggestions?
Piotras
_______________________________________________
dev mailing list
dev@lists.midgard-project.org
http://lists.midgard-project.org/mailman/listinfo/dev -
