David Mitchell
2005-Jul-27 01:48 UTC
Displaying selected items in a many-to-many relationship
Hello list,
I''ve got three tables involved in a many-to-many relationship:
- apps
- assets
- apps_assets
What I want to do is display a list of apps (simple enough, using a
scaffold), have the user select one from the list, then display all
the assets related to that app. Instead, when the user selects an app
from the list, *all* assets get displayed.
First, the data in the apps_assets table is correct.
When the user clicks on an app, the URL that gets generated is e.g.
http://localhost:3000/apps/assets/100, which looks OK.
In my routes, I''ve got an entry
map.connect ''apps/assets/:id'', :controller =>
''assets'', :action =>
''list_assets_for_app''
Within assets_controller.rb, I''ve got
def list_assets_for_app
unless @params[''id''].nil?
@assets = Asset.find_by_sql("select * from assets, apps_assets
where apps_assets.app_id = " + @params[''id].to_s + " and
apps_assets.asset_id = assets.id")
end
@asset_pages, @assets = paginate :assets, :per_page => 10
render :action => ''list''
end
What I''m trying to do here is use the find_by_sql statement to select
only those assets that are related to the user-selected app. By
putting in ''render_text'' statements in various places,
I''ve convinced
myself of the following:
- the find_by_sql statement is correct, and returns only those assets
related to the chosen app
- after the ''@assets = Asset.find_by_sql'' line of code,
@assets.length
is the size it should be
However, after exiting the unless...end block (i.e. one line later),
@assets.length is the same as the number of entries in the entire
assets table. In other words, @assets seems to have now ignored my
find_by_sql statement entirely!
Could someone please tell me where I''m going wrong?
Thanks in advance
Dave M.
Cuong Tran
2005-Jul-27 01:55 UTC
Re: Displaying selected items in a many-to-many relationship
Check out this link: http://wiki.rubyonrails.com/rails/show/How+to+Paginate+With+Ajax (you need to pass the id to paginate and your find_by_sql is unnecessary) On 7/26/05, David Mitchell <monch1962-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> Hello list, > > I''ve got three tables involved in a many-to-many relationship: > - apps > - assets > - apps_assets > > What I want to do is display a list of apps (simple enough, using a > scaffold), have the user select one from the list, then display all > the assets related to that app. Instead, when the user selects an app > from the list, *all* assets get displayed. > > First, the data in the apps_assets table is correct. > > When the user clicks on an app, the URL that gets generated is e.g. > http://localhost:3000/apps/assets/100, which looks OK. > > In my routes, I''ve got an entry > map.connect ''apps/assets/:id'', :controller => ''assets'', :action => > ''list_assets_for_app'' > > Within assets_controller.rb, I''ve got > def list_assets_for_app > unless @params[''id''].nil? > @assets = Asset.find_by_sql("select * from assets, apps_assets > where apps_assets.app_id = " + @params[''id].to_s + " and > apps_assets.asset_id = assets.id") > end > @asset_pages, @assets = paginate :assets, :per_page => 10 > render :action => ''list'' > end > > What I''m trying to do here is use the find_by_sql statement to select > only those assets that are related to the user-selected app. By > putting in ''render_text'' statements in various places, I''ve convinced > myself of the following: > - the find_by_sql statement is correct, and returns only those assets > related to the chosen app > - after the ''@assets = Asset.find_by_sql'' line of code, @assets.length > is the size it should be > > However, after exiting the unless...end block (i.e. one line later), > @assets.length is the same as the number of entries in the entire > assets table. In other words, @assets seems to have now ignored my > find_by_sql statement entirely! > > Could someone please tell me where I''m going wrong? > > Thanks in advance > > Dave M. > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >
David Mitchell
2005-Jul-27 05:05 UTC
Re: Displaying selected items in a many-to-many relationship
I checked the link Tran sent me, and it talks to a one-to-many
relationship rather than a many-to-many. I changed my paginate
statement to:
@asset_pages, @assets = paginate :asset,
:join => ",apps_assets",
:condtions => "apps_assets.app_id = #{@params[''id'']}
AND
apps_assets.asset_id = assets.id",
:per_page => 10
Note the comma (",apps_assets") in the :join is required, due to a bug
(http://dev.rubyonrails.org/ticket/1288) which is closed but not yet
in non-beta Rails...
Anyhoo, I''m getting a strange message now:
ActiveRecord::StatementInvalid in Assets#list_assets_for_app
ORA-00918: column ambiguously defined: select * from (select
raw_sql_.*, rownum raw_rnum_ from (SELECT * FROM assets ,apps_assets
WHERE apps_assets.app_id = 100 AND apps_assets.asset_id = assets.id )
raw_sql_ where rownum <= 10) where raw_rnum > 0
A quick check of the SQL shows there''s no ambiguity in *my* SQL (i.e.
"SELECT * FROM assets ,apps_assets WHERE apps_assets.app_id = 100
AND apps_assets.asset_id = assets.id" has a table name for every field
name), so I''m stumped again.
Has anyone got any suggestions at this point?
Thanks again
Dave M.
On 7/27/05, Cuong Tran
<cuong.tran-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:> Check out this link:
>
> http://wiki.rubyonrails.com/rails/show/How+to+Paginate+With+Ajax
>
> (you need to pass the id to paginate and your find_by_sql is unnecessary)
>
> On 7/26/05, David Mitchell
<monch1962-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
> > Hello list,
> >
> > I''ve got three tables involved in a many-to-many
relationship:
> > - apps
> > - assets
> > - apps_assets
> >
> > What I want to do is display a list of apps (simple enough, using a
> > scaffold), have the user select one from the list, then display all
> > the assets related to that app. Instead, when the user selects an app
> > from the list, *all* assets get displayed.
> >
> > First, the data in the apps_assets table is correct.
> >
> > When the user clicks on an app, the URL that gets generated is e.g.
> > http://localhost:3000/apps/assets/100, which looks OK.
> >
> > In my routes, I''ve got an entry
> > map.connect ''apps/assets/:id'', :controller =>
''assets'', :action =>
> > ''list_assets_for_app''
> >
> > Within assets_controller.rb, I''ve got
> > def list_assets_for_app
> > unless @params[''id''].nil?
> > @assets = Asset.find_by_sql("select * from assets,
apps_assets
> > where apps_assets.app_id = " + @params[''id].to_s +
" and
> > apps_assets.asset_id = assets.id")
> > end
> > @asset_pages, @assets = paginate :assets, :per_page => 10
> > render :action => ''list''
> > end
> >
> > What I''m trying to do here is use the find_by_sql statement
to select
> > only those assets that are related to the user-selected app. By
> > putting in ''render_text'' statements in various
places, I''ve convinced
> > myself of the following:
> > - the find_by_sql statement is correct, and returns only those assets
> > related to the chosen app
> > - after the ''@assets = Asset.find_by_sql'' line of
code, @assets.length
> > is the size it should be
> >
> > However, after exiting the unless...end block (i.e. one line later),
> > @assets.length is the same as the number of entries in the entire
> > assets table. In other words, @assets seems to have now ignored my
> > find_by_sql statement entirely!
> >
> > Could someone please tell me where I''m going wrong?
> >
> > Thanks in advance
> >
> > Dave M.
> > _______________________________________________
> > Rails mailing list
> > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> > http://lists.rubyonrails.org/mailman/listinfo/rails
> >
>
Cuong Tran
2005-Jul-27 05:22 UTC
Re: Displaying selected items in a many-to-many relationship
Try this:
@asset_pages, @assets = paginate :asset,
:join => "inner join apps_assets aa on aa.asset_id = id",
:conditions => ["aa.app_id = ?", params[:id]],
:per_page => 10
fyi, use place holder "?" for better security:
http://manuals.rubyonrails.com/read/chapter/43#page123
On 7/27/05, David Mitchell
<monch1962-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:> I checked the link Tran sent me, and it talks to a one-to-many
> relationship rather than a many-to-many. I changed my paginate
> statement to:
> @asset_pages, @assets = paginate :asset,
> :join => ",apps_assets",
> :condtions => "apps_assets.app_id =
#{@params[''id'']} AND
> apps_assets.asset_id = assets.id",
> :per_page => 10
>
> Note the comma (",apps_assets") in the :join is required, due to
a bug
> (http://dev.rubyonrails.org/ticket/1288) which is closed but not yet
> in non-beta Rails...
>
> Anyhoo, I''m getting a strange message now:
>
> ActiveRecord::StatementInvalid in Assets#list_assets_for_app
> ORA-00918: column ambiguously defined: select * from (select
> raw_sql_.*, rownum raw_rnum_ from (SELECT * FROM assets ,apps_assets
> WHERE apps_assets.app_id = 100 AND apps_assets.asset_id = assets.id )
> raw_sql_ where rownum <= 10) where raw_rnum > 0
>
> A quick check of the SQL shows there''s no ambiguity in *my* SQL
(i.e.
> "SELECT * FROM assets ,apps_assets WHERE apps_assets.app_id = 100
> AND apps_assets.asset_id = assets.id" has a table name for every field
> name), so I''m stumped again.
>
> Has anyone got any suggestions at this point?
>
> Thanks again
>
> Dave M.
>
> On 7/27/05, Cuong Tran
<cuong.tran-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
> > Check out this link:
> >
> > http://wiki.rubyonrails.com/rails/show/How+to+Paginate+With+Ajax
> >
> > (you need to pass the id to paginate and your find_by_sql is
unnecessary)
> >
> > On 7/26/05, David Mitchell
<monch1962-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
> > > Hello list,
> > >
> > > I''ve got three tables involved in a many-to-many
relationship:
> > > - apps
> > > - assets
> > > - apps_assets
> > >
> > > What I want to do is display a list of apps (simple enough, using
a
> > > scaffold), have the user select one from the list, then display
all
> > > the assets related to that app. Instead, when the user selects
an app
> > > from the list, *all* assets get displayed.
> > >
> > > First, the data in the apps_assets table is correct.
> > >
> > > When the user clickson an app, the URL that gets generated is
e.g.
> > > http://localhost:3000/apps/assets/100, which looks OK.
> > >
> > > In my routes, I''ve got an entry
> > > map.connect ''apps/assets/:id'', :controller
=> ''assets'', :action =>
> > > ''list_assets_for_app''
> > >
> > > Within assets_controller.rb, I''ve got
> > > def list_assets_for_app
> > > unless @params[''id''].nil?
> > > @assets = Asset.find_by_sql("select * from assets,
apps_assets
> > > where apps_assets.app_id = " + @params[''id].to_s +
" and
> > > apps_assets.asset_id = assets.id")
> > > end
> > > @asset_pages, @assets = paginate :assets, :per_page => 10
> > > render :action => ''list''
> > > end
> > >
> > > What I''m trying to do here is use the find_by_sql
statement to select
> > > only those assets that are related to the user-selected app. By
> > > putting in ''render_text'' statements in various
places, I''ve convinced
> > > myself of the following:
> > > - the find_by_sql statement is correct, and returns only those
assets
> > > related to the chosen app
> > > - after the ''@assets = Asset.find_by_sql'' line
of code, @assets.length
> > > is the size it should be
> > >
> > > However, after exiting the unless...end block (i.e. one line
later),
> > > @assets.length is the same as the number of entries in the entire
> > > assets table. In other words, @assets seems to have now ignored
my
> > > find_by_sql statement entirely!
> > >
> > > Could someone please tell me where I''m going wrong?
> > >
> > > Thanks in advance
> > >
> > > Dave M.
> > > _______________________________________________
> > > Rails mailing list
> > > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> > > http://lists.rubyonrails.org/mailman/listinfo/rails
> > >
> >
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>
David Mitchell
2005-Jul-27 05:59 UTC
Re: Displaying selected items in a many-to-many relationship
Thanks for all your help, Tran. Got it solved, but not quite as you suggested. I''m developing in Oracle 8i, but production is Oracle 10g. Unfortunately, 8i doesn''t support INNER JOIN properly, while 10g does (thanks to Google for identifying this, because I thought I was going insane having something that worked fine on 10g but not 8i!!). Instead, I found @asset_pages, @assets = paginate :asset, :conditions => ["id in (select asset_id from apps_assets where app_id = ?)", params[:id]], :per_page => 10 works perfectly. I''ll write it up in the Wiki when I get home tonight. Dave M. On 7/27/05, Cuong Tran <cuong.tran-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> Try this: > > @asset_pages, @assets = paginate :asset, > :join => "inner join apps_assets aa on aa.asset_id = id", > :conditions => ["aa.app_id = ?", params[:id]], > :per_page => 10 > > fyi, use place holder "?" for better security: > > http://manuals.rubyonrails.com/read/chapter/43#page123 > > On 7/27/05, David Mitchell <monch1962-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > I checked the link Tran sent me, and it talks to a one-to-many > > relationship rather than a many-to-many. I changed my paginate > > statement to: > > @asset_pages, @assets = paginate :asset, > > :join => ",apps_assets", > > :condtions => "apps_assets.app_id = #{@params[''id'']} AND > > apps_assets.asset_id = assets.id", > > :per_page => 10 > > > > Note the comma (",apps_assets") in the :join is required, due to a bug > > (http://dev.rubyonrails.org/ticket/1288) which is closed but not yet > > in non-beta Rails... > > > > Anyhoo, I''m getting a strange message now: > > > > ActiveRecord::StatementInvalid in Assets#list_assets_for_app > > ORA-00918: column ambiguously defined: select * from (select > > raw_sql_.*, rownum raw_rnum_ from (SELECT * FROM assets ,apps_assets > > WHERE apps_assets.app_id = 100 AND apps_assets.asset_id = assets.id ) > > raw_sql_ where rownum <= 10) where raw_rnum > 0 > > > > A quick check of the SQL shows there''s no ambiguity in *my* SQL (i.e. > > "SELECT * FROM assets ,apps_assets WHERE apps_assets.app_id = 100 > > AND apps_assets.asset_id = assets.id" has a table name for every field > > name), so I''m stumped again. > > > > Has anyone got any suggestions at this point? > > > > Thanks again > > > > Dave M. > > > > On 7/27/05, Cuong Tran <cuong.tran-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > > Check out this link: > > > > > > http://wiki.rubyonrails.com/rails/show/How+to+Paginate+With+Ajax > > > > > > (you need to pass the id to paginate and your find_by_sql is unnecessary) > > > > > > On 7/26/05, David Mitchell <monch1962-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > > > Hello list, > > > > > > > > I''ve got three tables involved in a many-to-many relationship: > > > > - apps > > > > - assets > > > > - apps_assets > > > > > > > > What I want to do is display a list of apps (simple enough, using a > > > > scaffold), have the user select one from the list, then display all > > > > the assets related to that app. Instead, when the user selects an app > > > > from the list, *all* assets get displayed. > > > > > > > > First, the data in the apps_assets table is correct. > > > > > > > > When the user clickson an app, the URL that gets generated is e.g. > > > > http://localhost:3000/apps/assets/100, which looks OK. > > > > > > > > In my routes, I''ve got an entry > > > > map.connect ''apps/assets/:id'', :controller => ''assets'', :action => > > > > ''list_assets_for_app'' > > > > > > > > Within assets_controller.rb, I''ve got > > > > def list_assets_for_app > > > > unless @params[''id''].nil? > > > > @assets = Asset.find_by_sql("select * from assets, apps_assets > > > > where apps_assets.app_id = " + @params[''id].to_s + " and > > > > apps_assets.asset_id = assets.id") > > > > end > > > > @asset_pages, @assets = paginate :assets, :per_page => 10 > > > > render :action => ''list'' > > > > end > > > > > > > > What I''m trying to do here is use the find_by_sql statement to select > > > > only those assets that are related to the user-selected app. By > > > > putting in ''render_text'' statements in various places, I''ve convinced > > > > myself of the following: > > > > - the find_by_sql statement is correct, and returns only those assets > > > > related to the chosen app > > > > - after the ''@assets = Asset.find_by_sql'' line of code, @assets.length > > > > is the size it should be > > > > > > > > However, after exiting the unless...end block (i.e. one line later), > > > > @assets.length is the same as the number of entries in the entire > > > > assets table. In other words, @assets seems to have now ignored my > > > > find_by_sql statement entirely! > > > > > > > > Could someone please tell me where I''m going wrong? > > > > > > > > Thanks in advance > > > > > > > > Dave M. > > > > _______________________________________________ > > > > Rails mailing list > > > > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > > > > http://lists.rubyonrails.org/mailman/listinfo/rails > > > > > > > > > _______________________________________________ > > Rails mailing list > > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > > http://lists.rubyonrails.org/mailman/listinfo/rails > > >
Cuong Tran
2005-Jul-27 14:27 UTC
Re: Displaying selected items in a many-to-many relationship
I missed the oracle part :) On 7/27/05, David Mitchell <monch1962-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> Thanks for all your help, Tran. > > Got it solved, but not quite as you suggested. > > I''m developing in Oracle 8i, but production is Oracle 10g. > Unfortunately, 8i doesn''t support INNER JOIN properly, while 10g does > (thanks to Google for identifying this, because I thought I was going > insane having something that worked fine on 10g but not 8i!!). > > Instead, I found > @asset_pages, @assets = paginate :asset, > :conditions => ["id in (select asset_id from apps_assets where > app_id = ?)", params[:id]], > :per_page => 10 > works perfectly. > > I''ll write it up in the Wiki when I get home tonight. > > Dave M. > > On 7/27/05, Cuong Tran <cuong.tran-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > Try this: > > > > @asset_pages, @assets = paginate :asset, > > :join => "inner join apps_assets aa on aa.asset_id = id", > > :conditions => ["aa.app_id = ?", params[:id]], > > :per_page => 10 > > > > fyi, use place holder "?" for better security: > > > > http://manuals.rubyonrails.com/read/chapter/43#page123 > > > > On 7/27/05, David Mitchell <monch1962-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > > I checked the link Tran sent me, and it talks to a one-to-many > > > relationship rather than a many-to-many. I changed my paginate > > > statement to: > > > @asset_pages, @assets = paginate :asset, > > > :join => ",apps_assets", > > > :condtions => "apps_assets.app_id = #{@params[''id'']} AND > > > apps_assets.asset_id = assets.id", > > > :per_page => 10 > > > > > > Note the comma (",apps_assets") in the :join is required, due to a bug > > > (http://dev.rubyonrails.org/ticket/1288) which is closed but not yet > > > in non-beta Rails... > > > > > > Anyhoo, I''m getting a strange message now: > > > > > > ActiveRecord::StatementInvalid in Assets#list_assets_for_app > > > ORA-00918: column ambiguously defined: select * from (select > > > raw_sql_.*, rownum raw_rnum_ from (SELECT * FROM assets ,apps_assets > > > WHERE apps_assets.app_id = 100 AND apps_assets.asset_id = assets.id ) > > > raw_sql_ where rownum <= 10) where raw_rnum > 0 > > > > > > A quick check of the SQL shows there''s no ambiguity in *my* SQL (i.e. > > > "SELECT * FROM assets ,apps_assets WHERE apps_assets.app_id = 100 > > > AND apps_assets.asset_id = assets.id" has a table name for every field > > > name), so I''m stumped again. > > > > > > Has anyone got any suggestions at this point? > > > > > > Thanks again > > > > > > Dave M. > > > > > > On 7/27/05, Cuong Tran <cuong.tran-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > > > Check out this link: > > > > > > > > http://wiki.rubyonrails.com/rails/show/How+to+Paginate+With+Ajax > > > > > > > > (you need to pass the id to paginate and your find_by_sql is unnecessary) > > > > > > > > On 7/26/05, David Mitchell <monch1962-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > > > > Hello list, > > > > > > > > > > I''ve got three tables involved in a many-to-many relationship: > > > > > - apps > > > > > - assets > > > > > - apps_assets > > > > > > > > > > What I want to do is display a list of apps (simple enough, using a > > > > > scaffold), have the user select one from the list, then display all > > > > > the assets related to that app. Instead, when the user selects an app > > > > > from the list, *all* assets get displayed. > > > > > > > > > > First, the data in the apps_assets table is correct. > > > > > > > > > > When the user clickson an app, the URL that gets generated is e.g. > > > > > http://localhost:3000/apps/assets/100, which looks OK. > > > > > > > > > > In my routes, I''ve got an entry > > > > > map.connect ''apps/assets/:id'', :controller => ''assets'', :action => > > > > > ''list_assets_for_app'' > > > > > > > > > > Within assets_controller.rb, I''ve got > > > > > def list_assets_for_app > > > > > unless @params[''id''].nil? > > > > > @assets = Asset.find_by_sql("select * from assets, apps_assets > > > > > where apps_assets.app_id = " + @params[''id].to_s + " and > > > > > apps_assets.asset_id = assets.id") > > > > > end > > > > > @asset_pages, @assets = paginate :assets, :per_page => 10 > > > > > render :action => ''list'' > > > > > end > > > > > > > > > > What I''m trying to do here is use the find_by_sql statement to select > > > > > only those assets that are related to the user-selected app. By > > > > > putting in ''render_text'' statements in various places, I''ve convinced > > > > > myself of the following: > > > > > - the find_by_sql statement is correct, and returns only those assets > > > > > related to the chosen app > > > > > - after the ''@assets = Asset.find_by_sql'' line of code, @assets.length > > > > > is the size it should be > > > > > > > > > > However, after exiting the unless...end block (i.e. one line later), > > > > > @assets.length is the same as the number of entries in the entire > > > > > assets table. In other words, @assets seems to have now ignored my > > > > > find_by_sql statement entirely! > > > > > > > > > > Could someone please tell me where I''m going wrong? > > > > > > > > > > Thanks in advance > > > > > > > > > > Dave M. > > > > > _______________________________________________ > > > > > Rails mailing list > > > > > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > > > > > http://lists.rubyonrails.org/mailman/listinfo/rails > > > > > > > > > > > > _______________________________________________ > > > Rails mailing list > > > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > > > http://lists.rubyonrails.org/mailman/listinfo/rails > > > > > >