Finally I found the solution to get the tags, but run into another issue
with CONTAINS (vs BELONGS).
The solution is to use alias with nested select:
Bin = db.bins.with_alias('bin')
tags = db(Bin.id==db.bins.id)._select(db.bins.tags_id)
rows = db(db.bins.id>1).select(
left=db.notes.on( (db.notes.tags_id.contains(tags)) )
)
But I have a new problem now. For example,
tags = (1,2,3,4) <-- this is the control set, which the nested select
should retrieve
set_A = (7,8,9)
set_B = (1,2)
set_C = (3,)
set_D = (4,5,6)
I want to get set_B, set_C, set_D, because they contain at least one of the
elements that are available in "tags".
So I use contains() instead of belongs()
But the generated SQL is
SELECT ... FROM bins LEFT JOIN notes ON ((notes.bin_id = bins.id) OR
(notes.tags_id LIKE '%|SELECT bins.tags_id FROM bins AS bin, bins WHERE
(bin.id = bins.id);|%')) WHERE (bins.id > 1);
Which is slightly different from what it is supposed to generate...
SELECT ... FROM bins LEFT JOIN notes ON ((notes.bin_id = bins.id) OR
((((notes.tags_id LIKE '%|1|%') OR (notes.tags_id LIKE '%|2|%')) OR
(notes.tags_id LIKE '%|3|%')) OR (notes.tags_id LIKE '%|4|%'))) WHERE (bins.id
> 1);
Am I in the wrong direction?
On Monday, October 15, 2012 5:19:13 PM UTC+8, lyn2py wrote:
>
> The field (*db.bins.tags_id*) has contents of a list:reference type, i.e.
> *|1|2|6|8|*
>
> I need to get it into a list (i.e. *[1,2,6,8]*) to be used in a DAL
> .select(). How should I do this?
>
> My code:
> rows = db(db.bins.id>1).select(
> left=db.notes.on(
> (db.notes.tags_id.contains((*db.bins.tags_id*).split('|'))
> )
> )
>
> As you can see, I tried to use the python split, but it returned an error.
> Any tips on how to proceed to make this work?
>
> Thanks!
>
--
