[web2py] Re: Adding to derived smartgrid constraints.
I had a slightly different case, maybe useful to share. I had a table
"model" with two references to "make" and I wanted the models in the grid,
but filtered by one of the two referenced fields of the "make" table.
db.py
db.define_table('make',
Field('name'))
Make_one = db.make.with_alias('make_one')
Make_two = db.make.with_alias('make_two')
db.define_table('model',
Field('name'),
Field('make_one_id', type='reference make_one'),
Field('make_two_id', type='reference make_two'),
Field('options', 'list:reference option'))
default.py
def index():
#db.make.insert(id=1, name='Ford')
#db.make.insert(id=2, name='Fiat')
#db.make.insert(id=3, name='Toyota')
#db.model.insert(name='A',make_one_id=1,make_two_id=2)
#db.model.insert(name='B',make_one_id=1,make_two_id=3)
#db.model.insert(name='C',make_one_id=2,make_two_id=3)
query = db.model.make_one_id==db(Make_one.name=='Ford').select(Make_one.
id).first()
constraints = {'model':query}
grid = SQLFORM.smartgrid(db.model,constraints=constraints)
return locals()
index.html
{{extend 'layout.html'}}
{{=grid}}
On Tuesday, October 2, 2012 11:52:43 PM UTC+9, Massimo Di Pierro wrote:
>
> You have other problems before you solve this one.
>
> your model<->option is a one-to-many but should be a many-to-many (many
> cars can have alloy-wheels and alloy-wheel is one of many possible options
> for each car).
>
> Grid does now allow many-to-many very well but you can try:
>
>
> db.define_table('make',
> Field('name'))
>
> db.define_table('option',
> Field('name'))
>
> db.define_table('model',
> Field('name'),
> Field('make_id', db.make),
> Field('options', 'list:reference option'))
>
> Then I make a controller as follows:
>
> def index():
> query = db.model.options.contains(db.option(name='Alloy Wheels').id)
> constraints = {'model':query}
> grid = SQLFORM.smartgrid(db.make,constraints=constraints)
> return dict(grid=grid)
>
> On Tuesday, 2 October 2012 07:27:19 UTC-5, Dominic Cioccarelli wrote:
>>
>> I already posted this as a continuation of an existing question, but I
>> figured it may be better to break it out into a separate thread...
>>
>> What happens if I want the constraint to add to the existing constraints
>> that have been built by smartgrid? For example, if I have the model:
>>
>> db.define_table('make',
>> Field('name'))
>>
>> db.define_table('option',
>> Field('name'))
>>
>> m3t.define_table('model',
>> Field('name'),
>> Field('make_id', db.make),
>> Field('option_id', db.option))
>>
>> Then I make a controller as follows:
>>
>> def index():
>> grid = SQLFORM.smartgrid(db.make,constraints=constraints)
>> return dict(grid=grid)
>>
>> If I navigate to (for example)
>>
>> Alfa Romeo -> Gulia (Gulia being a type of Alfa)
>>
>> And I want to only display Gulias with alloy wheels, which constraint
>> should I define?
>>
>> If I define something like:
>>
>> query = option.name == 'Alloy Wheels'
>> constraints = {'model':query}
>>
>> ... I'll get all the cars (irrespective of make and model) that have
>> alloy wheels. What I want is all the Alfa Romeo Gulias with Alloy Wheels.
>> On the other hand I remove the constraint and navigate from "Alfa Romeo" to
>> "Gulia" I'll get all Alfa Romo Gulias. What I need is a constraint that is
>> *added* to the generated constraints.
>>
>> Many thanks in advance,
>> Dominic.
>>
>>
--
[web2py] Re: Adding to derived smartgrid constraints.
Hi Massimo,
many thanks. Actually, the problem was when I tried to make a general
example out of my business specific problem ;-) Before I "translated" my
problem into an example, there was actually not a many-to-many
relationship. For the purpose of this exercise, let's just assume that all
options are specific to a car (which is the case in my real problem).
Therefore model-option is now a 1:N relationship.
I did get the constraint query to work, but I was slightly surprised by
what I needed to do. What finally worked was:
query = (db.model.option_id == db.option.id) & (db.option.name == 'Alloy
Wheels')
Given that in my model, options were already related to models:
m3t.define_table('model',
Field('name'),
Field('make_id', db.make),
Field('option_id', db.option))
I would have expected to be able to do:
query = db.model.option.name == 'Alloy Wheels'
... but it complains that "option" isn't a valid field in "model".
In any case, it is working now but I still would have thought I could have
simply recursed the object hierarchy to build the query (as per something
like Hibernate).
Cheers,
Dominic.
On Tuesday, 2 October 2012 16:52:43 UTC+2, Massimo Di Pierro wrote:
>
> You have other problems before you solve this one.
>
> your model<->option is a one-to-many but should be a many-to-many (many
> cars can have alloy-wheels and alloy-wheel is one of many possible options
> for each car).
>
> Grid does now allow many-to-many very well but you can try:
>
>
> db.define_table('make',
> Field('name'))
>
> db.define_table('option',
> Field('name'))
>
> db.define_table('model',
> Field('name'),
> Field('make_id', db.make),
> Field('options', 'list:reference option'))
>
> Then I make a controller as follows:
>
> def index():
> query = db.model.options.contains(db.option(name='Alloy Wheels').id)
> constraints = {'model':query}
> grid = SQLFORM.smartgrid(db.make,constraints=constraints)
> return dict(grid=grid)
>
> On Tuesday, 2 October 2012 07:27:19 UTC-5, Dominic Cioccarelli wrote:
>>
>> I already posted this as a continuation of an existing question, but I
>> figured it may be better to break it out into a separate thread...
>>
>> What happens if I want the constraint to add to the existing constraints
>> that have been built by smartgrid? For example, if I have the model:
>>
>> db.define_table('make',
>> Field('name'))
>>
>> db.define_table('option',
>> Field('name'))
>>
>> m3t.define_table('model',
>> Field('name'),
>> Field('make_id', db.make),
>> Field('option_id', db.option))
>>
>> Then I make a controller as follows:
>>
>> def index():
>> grid = SQLFORM.smartgrid(db.make,constraints=constraints)
>> return dict(grid=grid)
>>
>> If I navigate to (for example)
>>
>> Alfa Romeo -> Gulia (Gulia being a type of Alfa)
>>
>> And I want to only display Gulias with alloy wheels, which constraint
>> should I define?
>>
>> If I define something like:
>>
>> query = option.name == 'Alloy Wheels'
>> constraints = {'model':query}
>>
>> ... I'll get all the cars (irrespective of make and model) that have
>> alloy wheels. What I want is all the Alfa Romeo Gulias with Alloy Wheels.
>> On the other hand I remove the constraint and navigate from "Alfa Romeo" to
>> "Gulia" I'll get all Alfa Romo Gulias. What I need is a constraint that is
>> *added* to the generated constraints.
>>
>> Many thanks in advance,
>> Dominic.
>>
>>
--
[web2py] Re: Adding to derived smartgrid constraints.
You have other problems before you solve this one.
your model<->option is a one-to-many but should be a many-to-many (many
cars can have alloy-wheels and alloy-wheel is one of many possible options
for each car).
Grid does now allow many-to-many very well but you can try:
db.define_table('make',
Field('name'))
db.define_table('option',
Field('name'))
db.define_table('model',
Field('name'),
Field('make_id', db.make),
Field('options', 'list:reference option'))
Then I make a controller as follows:
def index():
query = db.model.options.contains(db.option(name='Alloy Wheels').id)
constraints = {'model':query}
grid = SQLFORM.smartgrid(db.make,constraints=constraints)
return dict(grid=grid)
On Tuesday, 2 October 2012 07:27:19 UTC-5, Dominic Cioccarelli wrote:
>
> I already posted this as a continuation of an existing question, but I
> figured it may be better to break it out into a separate thread...
>
> What happens if I want the constraint to add to the existing constraints
> that have been built by smartgrid? For example, if I have the model:
>
> db.define_table('make',
> Field('name'))
>
> db.define_table('option',
> Field('name'))
>
> m3t.define_table('model',
> Field('name'),
> Field('make_id', db.make),
> Field('option_id', db.option))
>
> Then I make a controller as follows:
>
> def index():
> grid = SQLFORM.smartgrid(db.make,constraints=constraints)
> return dict(grid=grid)
>
> If I navigate to (for example)
>
> Alfa Romeo -> Gulia (Gulia being a type of Alfa)
>
> And I want to only display Gulias with alloy wheels, which constraint
> should I define?
>
> If I define something like:
>
> query = option.name == 'Alloy Wheels'
> constraints = {'model':query}
>
> ... I'll get all the cars (irrespective of make and model) that have alloy
> wheels. What I want is all the Alfa Romeo Gulias with Alloy Wheels. On the
> other hand I remove the constraint and navigate from "Alfa Romeo" to
> "Gulia" I'll get all Alfa Romo Gulias. What I need is a constraint that is
> *added* to the generated constraints.
>
> Many thanks in advance,
> Dominic.
>
>
--
