can not fetch data from other table

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 !

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.

:slight_smile:
Bala

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.
:slight_smile:
Bala

thx

Dharmdip Rathod wrote:

thx

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

: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

:slight_smile:
Bala

undefined method `country' for "Newyork":String
i am getting this error !

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

:slight_smile:

Bala

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 !

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

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

<p>URL : <%= @site_detail.url%></p>
<p>TITLE: <%= @site_detail.title%></p>
<p>Email: <%= @site_detail.email%></p>
<p>Posting ID: <%= @site_detail.postingid%></p>
<p>Date: <%= @site_detail.date%></p>
<p>Price: $<%= @site_detail.price%></p>
<p>City: <%= @site_detail.location%></p>
<p>Country: <%=Location.find_by_sql("SELECT DISTINCT country, state
FROM locations WHERE city = '#{@site_detail.location}'").to_s%></p>

TITLE: Jersey City Indoor Parking

Email: hous-815440723@craigslist.org

Posting ID: 815440723

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 !

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

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…

Bala kishore Pulicherla wrote:

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

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

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

sorry data is not formatted !

:o

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

:frowning:

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

Bala kishore Pulicherla wrote:

:o

even though u say distinct(state),distinct(city) u'll get the array
only...

:frowning:

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

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

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

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??

Bala kishore Pulicherla wrote:

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 Rathod <

display the country and state of the particular city??

as per ur db design u can do up to

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%>

<% countries = Location.find(:all,:select => ['country'], :conditions => ['city =?',@site_detail.location])

<% for country in countries %>
Country Name :<%= country %>
<%end %>
<% states = Location.find(:all,:select => [‘state’], :conditions => [‘city =?’,@site_detail.location]) %>

<%for state in states %>
State :<%= state %>
<%end %>

:slight_smile: as ur city name is for many states and countries.

if u want the other way consult ur db designer who designed the DB :x

:slight_smile:
Bala