## Tuesday, August 5, 2008

### New domain notation

During the last Community Days, somebody (it was Joël from C2C, I think) complain about the simplicity of the domains. For example, you can't include a OR condition, or negate the domain.

Negate a domain is a false problem: you may just invert the operator. So, `[('active', '=', 1)]` become `[('active', '<>', 1)]`

But, for the OR operator, the problem is really there. An implicit AND operation is made between each part of the domain. `[('active', '=', 1), ('parent_id', '=', 42)]` is translate into a SQL query that look like `WHERE active = 1 AND parent_id = 42`

Yes, adding a OR condition, is not a bad idea, but, how to include it without breaking all the domains already written ? After some discussions, we decide to uses a pseudo polish notation, always with a implicit AND operator. Now you can prefix domain parts with a '&' or a '|' and group part using parenthesis.

Here are some example of domains and the resulting SQL where clause:

`[('foo', '=', 'bar')]`
`foo = 'bar'`

`[('id', 'in', [1,2,3])]`
`id in (1, 2, 3)`

`[('field', '=', 'value'), ('field', '<>', 42)]`
`( field = 'value' AND field <> 42 )`

`[('&', ('field', '<', 'value'), ('field', '>', 'value'))]`
`( field < 'value' AND field > 'value' )`

`[('|', ('field', '=', 'value'), ('field', '=', 'value'))]`
`( field = 'value' OR field = 'value' )`

`[('&', ('field1', '=', 'value'), ('field2', '=', 'value'), ('|', ('field3', '<>', 'value'), ('field4', '=', 'value')))]`
`( field1 = 'value' AND field2 = 'value' AND ( field3 <> 'value' OR field4 = 'value' ) )`

`[('&', ('|', ('a', '=', 1), ('b', '=', 2)), ('|', ('c', '=', 3), ('d', '=', 4)))]`
`( ( a = 1 OR b = 2 ) AND ( c = 3 OR d = 4 ) )`

`[('|', (('a', '=', 1), ('b', '=', 2)), (('c', '=', 3), ('d', '=', 4)))]`
`( ( a = 1 AND b = 2 ) OR ( c = 3 AND d = 4 ) )`

Don't hesitate to ask questions if you don't understand.

Stay tuned.

PS: Thank you to Stéphane for the base code of the tree.

#### 1 comment:

Raphaël Valyi said...

Hi Stephane, yeah I agree the current domain thing, while user friendly is somewhat limited.

Ideally I would like to be able to perform any select I want that just return the expect record fields among others eventually. I would like to be able to do group by and order like I want...

I think a nice API to get inspired by is the Rails ActiveRecord base find method and its :conditions param, or the find_by_sql method eventyally, see here:
http://api.rubyonrails.org/classes/ActiveRecord/Base.html

Now, I don't know if something backward compatible might be found, but I think that if you really want something useful in many situations, then you should look for something that map pretty much to an SQL without loosing too much of its power.

Still, having AND and OR operator is already something nice. But as I said I would be interested in adding an order criteria too at least.

Keep it up!

Raphaël Valyi.
Smile.fr