Can not fetch data from other table

dharmdip · September 2, 2008, 8:06am

hello ,
problem while i am trying this query

@location=Location.find_by_sql(“SELECT DISTINCT country FROM locations
WHERE state = ‘#{@site_details.location}’”)

in @site_details.location i am having city name Newyork base on this i
want related states and country on view file . i am not able to fetch
this data .

Thing id that when i found state from input i want city and country to
display
based on state !

please help !

dharmdip · September 2, 2008, 8:16am

Can u be some clear…
i hope ur trying to get the result like

@site_details.location.state.country.name

if location is a table rather than a entity in ur site_details table and
in
ur site_details table make location_id as an entity.

Bala

On Tue, Sep 2, 2008 at 11:36 AM, Dharmdip R. <

dharmdip · September 2, 2008, 8:32am

Bala kishore Pulicherla wrote:

Can u be some clear…
i hope ur trying to get the result like

@site_details.location.state.country.name

if location is a table rather than a entity in ur site_details table and
in
ur site_details table make location_id as an entity.

Bala

On Tue, Sep 2, 2008 at 11:36 AM, Dharmdip R. <

thx for ur rpl kishore but i am stuck here !

look , i am trying to get city and country form the database based on
which state i am getting from input
link if i got chicago as state i want to fetch
city =illinois and country=usa just this.
i have table locations and site_details i am having state in
site_details and all city, state and country in locations how to give
relation in RoR and how to fetch data relatively.
help !

dharmdip · September 2, 2008, 9:28am

I consider the fallowing models that u had

class SiteDetails < ActiveRecord::Base
belongs_to :state

class State < ActiveRecord::Base
has_many :site_details
belongs_to :city

class City < ActiveRecord::Base
has_many :states
belongs_to :country

class country < ActiveRecord::Base
has_many :cities

in ur controller

def show
@site_details = SiteDetail.find(params[:id])
end

in ur view show.html.erb

<%= @site_details.state.name %> Gives u the state name
<%= @site_details.state.city.name %> gives u the city name of the site
details

<%= @site_details.state.city.country.name %> gives u the country name

cities table(id,name,country_id)
states table(id,name,city_id)
countries table(id,name)

site_details table(id,state_id,ur_field1,ur_field2…)

Hope ur trying to do the same.

Bala

On Tue, Sep 2, 2008 at 12:02 PM, Dharmdip R. <

dharmdip · September 2, 2008, 11:12am

thx

dharmdip · September 2, 2008, 11:13am

Dharmdip R. wrote:

thx

but i have all fields in same table called locations …
there are fields name
id
country
city
state

dharmdip · September 2, 2008, 11:18am

:o

ok then i hope in ur site_details u had stored location_id even …

class Location < ActionRecord :: Base
has_many :site_details

class SiteDetail < ActiveRecord :: Base
belongs_to :location

if so u can get directly like

@site_details.locations.city
@site_details.locations.state
@site_details.locations.country

Bala

On Tue, Sep 2, 2008 at 2:43 PM, Dharmdip R. <

dharmdip · September 2, 2008, 11:29am

Bala kishore Pulicherla wrote:

:o

ok then i hope in ur site_details u had stored location_id even …

class Location < ActionRecord :: Base
has_many :site_details

class SiteDetail < ActiveRecord :: Base
belongs_to :location

if so u can get directly like

@site_details.locations.city
@site_details.locations.state
@site_details.locations.country

Bala

On Tue, Sep 2, 2008 at 2:43 PM, Dharmdip R. <

previously i go threw that but dnt know why not working i have gave same
relation but this same error was occurs. thanks for u on click support
.
[email protected]

dharmdip · September 2, 2008, 11:30am

undefined method `country’ for “Newyork”:String
i am getting this error !

dharmdip · September 2, 2008, 12:02pm

You have a nil object when you didn’t expect it!
The error occurred while evaluating nil.location

i am getting this ! i know there is some silly mistake but i am trying
to solve

!

;(

by the way thanks for u r support !

dharmdip · September 2, 2008, 11:52am

it seems that ur site_details.locations holds “name” instead of the
table

c the bottom line is

if ur table is like
locations(id,city,country,name)
and site_details like(id,location_id…)

then the above code works

but it seems that ur site_details.location itself contains
“country_name”…

so therz no relation b/w locations and site_details.

if u still want to use the same u can do like

@location = Location.find(:first,:conditions =>
[‘country=?’,@site_details.locations])

now u can do
@location.city
@location.state

Bala
On Tue, Sep 2, 2008 at 3:00 PM, Dharmdip R. <

dharmdip · September 2, 2008, 1:12pm

what ur gettig in the @site_details array

use puts @site_details.inspect in ur controller and check the result
wether
ur getting the @site_details.location or not

On Tue, Sep 2, 2008 at 3:32 PM, Dharmdip R. <

dharmdip · September 2, 2008, 1:19pm

i am trying ur suggestion after some time but while i am trying this
query

URL : <%= @site_detail.url%>

TITLE: <%= @site_detail.title%>

Email: <%= @site_detail.email%>

Posting ID: <%= @site_detail.postingid%>

Date: <%= @site_detail.date%>

Price: $<%= @site_detail.price%>

City: <%= @site_detail.location%>

Country: <%=Location.find_by_sql("SELECT DISTINCT country, state FROM locations WHERE city = '#{@site_detail.location}'").to_s%>

TITLE: Jersey City Indoor Parking

Email: [email protected]

Posting ID: 815440723

Date: 2008-08-27

Price: $200.0

City: Newyork

Country: ###############

i am getting detail like this in country !

meaning of ############## is total records in db. i have tried all
string convert type but not successful… thx i am trying ur solution !

dharmdip · September 2, 2008, 1:32pm

and ur selecting both the country and state at a time which results an
array
or a hash.
so u can do like

<% location = Location.find(:first,:conditions => [‘city
?’,@site_detail.location])%>

<% for loc in location %>
Country <%= loc.country %>
State:<%= loc.state %>

<%end -%>

if city is the unique in locations table…

On Tue, Sep 2, 2008 at 4:57 PM, bala kishore pulicherla
<[email protected]

dharmdip · September 2, 2008, 1:28pm

Is city name in the locations table is unique???

On Tue, Sep 2, 2008 at 4:49 PM, Dharmdip R. <

dharmdip · September 2, 2008, 1:34pm

sorry data is not formatted !

dharmdip · September 2, 2008, 1:36pm

:o

even though u say distinct(state),distinct(city) u’ll get the array
only…

make sure u maintain a relation b/w the site_detail and locations other
wise

On Tue, Sep 2, 2008 at 5:02 PM, Dharmdip R. <

dharmdip · September 2, 2008, 1:32pm

Bala kishore Pulicherla wrote:

Is city name in the locations table is unique???

On Tue, Sep 2, 2008 at 4:49 PM, Dharmdip R. <

NO,
id country city state

         1    usa    alabama    auburn
 2   usa   alabama   birmingham
      3   usa   alabama   columbus,GA
4   usa   alabama   dothan
 5   usa   alabama   florence / muscle shoals
 6   usa   alabama   gadsden-anniston
 7   usa   alabama   huntsville
 8   usa   alabama   mobile
 9   usa   alabama   montgomery
 10   usa   alabama   tuscaloosa

dharmdip · September 2, 2008, 1:43pm

Do u want to display all the states and countries for that city…???

or u want to display the country and state of the particular site??

On Tue, Sep 2, 2008 at 5:10 PM, Dharmdip R. <

dharmdip · September 2, 2008, 1:40pm

Bala kishore Pulicherla wrote:

:o

even though u say distinct(state),distinct(city) u’ll get the array
only…

make sure u maintain a relation b/w the site_detail and locations other
wise

On Tue, Sep 2, 2008 at 5:02 PM, Dharmdip R. <

this is simple thing even i know logic but cant convert it !
i am so helpless !
but u give me lots of support when i fire query in mysql i got result
but here not !
what to do i dont know ! but …
deadline is deadline no excuse !
:)-
thanks ! for ur support