select widget with lookup_select and sql

Hi All,

I'm having issues getting IC to jump through the hoops I
want it to
(or jumping through IC's hoops).  I need to restrict which
items
appear in a dropdown based on a query.

Quick DB overview:

listing table
----------
listing_id
many fields...

rooms table
---------
room_id
listing_id
other fields....

image table
--------
image_id
room_id
listing_id

When editing an image I'd like to have a list of rooms in a
drop down
based on rooms related to the same listing as the image. 
I've tried
putting a query in the extended.lookup_query box in the
admin UI (it's
labeled 'SQL for options').  The query looks like this:

SELECT room_id, room_name FROM listing_rooms WHERE
listing_id = [cgi item_id]

I realize I should escape the cgi value, but that's left out
of this example.

When I put that query in the field it's passed verbatim to
MySQL.
I've also tried using the [table-editor] with a
lookup_query.room_id="same query as above" and had
the same results.

Is there any way to interpolate the data put into
'lookup_query'?  Is
there another way to accomplish this short of manually
creating the
select box?  It seems that IC has the tools in place to do
this, but I
don't know how to tie them together to get the result I
want.

Thanks.
_______________________________________________
interchange-users mailing list
interchange-usersicdevgroup.org
http://www.icdevgroup.org/mailman/listinfo/interchan
ge-users

> When editing an image I'd like to have a list of rooms
in a drop down
> based on rooms related to the same listing as the
image.  I've tried
> putting a query in the extended.lookup_query box in the
admin UI (it's
> labeled 'SQL for options').  The query looks like
this:
>
> SELECT room_id, room_name FROM listing_rooms WHERE
listing_id = [cgi item_id]

Hi Again,

I've spent more time trying to get this to work the way I
expect and
couldn't get the sql to interpolate.  I also looked into how
to
populate the select widget manually, but had no luck there.

The solution I've been working on is to create a file to
include using
'include form' in the UI.  I set some scratch values to
indicate what
query, what to name the box, etc...  I'd rather be able to
use the
widgets provided by IC for this, but it doesn't seem
possible in this
case.   Regardless, this solution does get the box
displayed.

The problem is that I have to remove the field (room_id) to
remove the
standard select box which is displaying the wrong content. 
Once this
is done the mv_data_fields form element doesn't contain
room_id so the
custom select box I've built is ignored.

I must be on the wrong track for solving this.  Does anyone
have any
suggestions on a different direction or a better way to
accomplish
this?

Thanks.
_______________________________________________
interchange-users mailing list
interchange-usersicdevgroup.org
http://www.icdevgroup.org/mailman/listinfo/interchan
ge-users

On 3/21/07, Aaron Berg <ir.gathgmail.com> wrote:
> > When editing an image I'd like to have a list of
rooms in a drop down
> > based on rooms related to the same listing as the
image.  I've tried
> > putting a query in the extended.lookup_query box
in the admin UI (it's
> > labeled 'SQL for options').  The query looks like
this:
> >
> > SELECT room_id, room_name FROM listing_rooms WHERE
listing_id = [cgi item_id]
>
>
> I must be on the wrong track for solving this.  Does
anyone have any
> suggestions on a different direction or a better way to
accomplish
> this?
>

This is now solved with some help from Mike Heins.

I had been trying to set the query using meta data and had
also tried
something like:

[table-editor
	widget.room_id=select
	lookup_query.room_id="SELECT room_id, room_name
			    FROM listing_rooms
			    WHERE listing_id = [scratch listing_item_id]
				"
	no_top=1
]

This method was incorrectly escaping the query and passing
it
literally to MySQL.

Mike gave me this solution:

[table-editor
	widget.room_id=select
	lookup_query.room_id=`
				$Scratch-> =~ s/D+//g;
				return qq{
			    SELECT room_id, room_name
			    FROM listing_rooms
			    WHERE listing_id = $Scratch->
				};
			`
	no_top=1
]

With the query correctly escaped it works much better. 
_______________________________________________
interchange-users mailing list
interchange-usersicdevgroup.org
http://www.icdevgroup.org/mailman/listinfo/interchan
ge-users

"Aaron Berg" <ir.gathgmail.com> wrote:
> This is now solved with some help from Mike Heins.
> 
> I had been trying to set the query using meta data and
had also tried
> something like:
> 
> [table-editor
> 	widget.room_id=select
> 	lookup_query.room_id="SELECT room_id, room_name
> 			    FROM listing_rooms
> 			    WHERE listing_id = [scratch listing_item_id]
> 				"
> 	no_top=1
> ]
> 
> This method was incorrectly escaping the query and
passing it
> literally to MySQL.
> 
> Mike gave me this solution:
> 
> [table-editor
> 	widget.room_id=select
> 	lookup_query.room_id=`
> 				$Scratch-> =~ s/D+//g;
> 				return qq{
> 			    SELECT room_id, room_name
> 			    FROM listing_rooms
> 			    WHERE listing_id =
$Scratch->
> 				};
> 			`
> 	no_top=1
> ]
> 
> With the query correctly escaped it works much better.

>
I think you may have misunderstood the solution.

The problem doesn't appear to have been solved by altering
the quotes
or with a query value escape mechanism.  The problem appears
to have
been solved by removing all non-digit characters from the
"listing_item_id"
scratchpad value and ensuring that the scratchpad value is
actually
used as part of the query, instead of the [scratch] tag
being passed
verbatim (without interpolation).

You would probably get the same effect with the following
code:

    [pragma interpolate_itl_references 1]

    [table-editor
        no_top=1
        widget.room_id=select
        lookup_query.room_id=|
            SELECT  room_id, room_name
            FROM    listing_rooms
            WHERE   listing_id = [scratch
name=listing_item_id filter=digits]
    |]

-- 
   _/   _/  _/_/_/_/  _/    _/  _/_/_/  _/    _/
  _/_/_/   _/_/      _/    _/    _/    _/_/  _/   K e v i n 
 W a l s h
 _/ _/    _/          _/ _/     _/    _/  _/_/    kevincursor.biz
_/   _/  _/_/_/_/      _/    _/_/_/  _/    _/
_______________________________________________
interchange-users mailing list
interchange-usersicdevgroup.org
http://www.icdevgroup.org/mailman/listinfo/interchan
ge-users