If I do this:
x = Client.select(“DISTINCT(client_category)”)
Then I get an array of relations. Instead, what I need is the entire
row (model instance attributes) for each of the returned relations.
What is the syntax to obtain the complete records?
I cannot find a discussion of the select method in the current AR API
(ActiveRecord::Base) and so I am
having some difficulty in turning this into something I can use.
Queries don’t really work that way. You can’t really select the full
record
when using distinct client_category.
Lets say you have the following rows:
client_category, client_name
Cat1, Foo
Cat1, Bar
Cat2, Other
If you are selecting distinct client_category, and want the full row,
which
of the two “Cat1” rows should it return?
Tim S. wrote in post #975719:
Queries don’t really work that way. You can’t really select the full
record when using distinct client_category.Lets say you have the following rows:
client_category, client_name
Cat1, Foo
Cat1, Bar
Cat2, OtherIf you are selecting distinct client_category, and want the full row,
which
of the two “Cat1” rows should it return?
What I want to accomplish is to obtain one example of each value and the
entire row that contains it. How does one do that? I do not care which
row is returned but I only want one row for each distinct value.
On 18 January 2011 15:14, James B. [email protected] wrote:
If you are selecting distinct client_category, and want the full row,
which
of the two “Cat1” rows should it return?What I want to accomplish is to obtain one example of each value and the
entire row that contains it. How does one do that? I do not care which
row is returned but I only want one row for each distinct value.
Why? If you do not care which row is returned then presumably you do
not care what is in the rest of the row so why do you need it?
Colin
Colin L. wrote in post #975727:
On 18 January 2011 15:14, James B. [email protected] wrote:
If you are selecting distinct client_category, and want the full row,
which
of the two “Cat1” rows should it return?What I want to accomplish is to obtain one example of each value and the
entire row that contains it. How does one do that? I do not care which
row is returned but I only want one row for each distinct value.Why? If you do not care which row is returned then presumably you do
not care what is in the rest of the row so why do you need it?Colin
Because I want one representative row for each category.
On 18 January 2011 16:07, James B. [email protected] wrote:
Why? If you do not care which row is returned then presumably you do
not care what is in the rest of the row so why do you need it?Colin
Because I want one representative row for each category.
I am interested to know why, when you do not care what the rest of the
row contains.
Colin
On 18 January 2011 16:47, James B. [email protected] wrote:
approach.
Do you know how to accomplish what I desire? If so then I would
appreciate it if you would tell me.
No I do not. I was hoping that if I understood in detail why you want
this that I could suggest an alternative approach. I still do not
understand why you might want one representative row from each
category when you do not care what the rest of the row contains.
Colin
Colin L. wrote in post #975760:
I am interested to know why, when you do not care what the rest of the
row contains.
I have answered that question. I just want to quickly obtain one
representative row from each category. I can get the same result by
iterating over an array of possible values and doing a
find_by_category(x).first on each. I would prefer a less cumbersome
approach.
Do you know how to accomplish what I desire? If so then I would
appreciate it if you would tell me.
Robert W. wrote in post #975774:
This requirement in itself raises questions about your design in my
mind, but it’s your project and you’re closer to the problem domain.
This is not part of any design. I need a small sample of representative
test data from a live data set such that each row belongs to a different
category and all active categories are represented. Period.
On 18 January 2011 17:24, James B. [email protected] wrote:
…
This is not part of any design. I need a small sample of representative
test data from a live data set such that each row belongs to a different
category and all active categories are represented. Period.
If it is for testing you might be better using Machinist or Factory
Girl to generate the data. Then you can generate exactly the data
required to test each aspect of your app.
Colin
I need a small sample of representative test data from a live data set
such
that each row belongs to a different category and all active categories
are
represented. Period.
Now that we know your requirements, we can recommend a way to do it.
Selecting distinct is not the solution to the requirements you posed.
Here is an example of code that would fit your needs:
records = []
Category.all.each do |category|
records << category.clients.first
end
James B. wrote in post #975768:
Colin L. wrote in post #975760:
I am interested to know why, when you do not care what the rest of the
row contains.I have answered that question. I just want to quickly obtain one
representative row from each category. I can get the same result by
iterating over an array of possible values and doing a
find_by_category(x).first on each. I would prefer a less cumbersome
approach.Do you know how to accomplish what I desire? If so then I would
appreciate it if you would tell me.
This is actually a common issue with Object-Relational-Mapping (ORM),
such as ActiveRecord. Each instance of an ActiveRecord subclass
represent a specific row in a database table. Objects have identity, and
in an ORM system identity is maintained by mapping the database row’s
primary key to a specific ActiveRecord subclass instance.
What you have presented here is a SQL problem that doesn’t not map
easily into an ORM system. This is what others have been attempting
implying. What you have presented here is a SQL problem I’d suggest
using SQL to solve it. You should be able to run any arbitrary SQL you
want. All you need is the database connection and execute your SQL.
I’m not going to go out of my way to try to figure out the required SQL
to accomplish your goal. I don’t know of a way to solve your problem
through any ORM. You need to drop down to the SQL level to solve it if
it’s really something your design requires.
This requirement in itself raises questions about your design in my
mind, but it’s your project and you’re closer to the problem domain.
Tim S. wrote in post #975786:
Here is an example of code that would fit your needs:
records = []
Category.all.each do |category|
records << category.clients.first
end
Yes, that is what I ended up doing, more or less. What I did was this:
cset = []
Client.select(“DISTINCT(client_category)”).each do |c|
cset << Client.find_by_client_category(c.client_category)
end
However, since I was in the console when I ran into this situation I was
hoping that there existed a more direct way of accomplishing it ( in
other words on a single line ) and that I was simply ignorant of the
method. Thus my question.
Thank all of you for your comments and help.
Colin L. wrote in post #975784:
On 18 January 2011 17:24, James B. [email protected] wrote:
…
This is not part of any design. I need a small sample of representative
test data from a live data set such that each row belongs to a different
category and all active categories are represented. Period.If it is for testing you might be better using Machinist or Factory
Girl to generate the data. Then you can generate exactly the data
required to test each aspect of your app.
Even with a direct solution to your problem Colin, suggestion is still
quite valid. Testing using a live sampling of data can give a false
sense of confidence. The better approach to testing is to use a factory
to manufacture data specifically designed to test some aspect of your
model.
Testing should be automated, and exist to prevent the possibility of
invalid data from ever making it into the database. Not to validate
existing data that is already assumed to be valid.
On 18 January 2011 18:06, James B. [email protected] wrote:
cset = []
Client.select(“DISTINCT(client_category)”).each do |c|
cset << Client.find_by_client_category(c.client_category)
end
To solve it in SQL you would (probably) need sub-queries or unions to
join distinct rows for each type of category… all very messy (as has
been hinted).
Comparing your solution to Tim’s suggestion, I infer that you don’t
have “client_category” as an association? (ie: client belongs_to
:category).
You could use the “group_by” method for collections, and select the
first off each group - that saves you doing a query per client, at the
cost of doing one large query and a load of Ruby iteration.
Client.all.group_by(&:client_category).map(&:first)
… it’s also easily chainable to write on one line (but you could
compress your own solution to one line with .inject instead of .select
At the end of the day, you’re in the realms of doing some fudging to
get the data out you’re after (as you’ve discovered), as you’re not
doing something that’s a natural fit for the DB or ORM. Sorry if
that’s not massively helpful… just letting you know we feel your
pain.
Michael P. wrote in post #975879:
Comparing your solution to Tim’s suggestion, I infer that you don’t
have “client_category” as an association? (ie: client belongs_to
:category).
No, we do not have any association by that name. The data originates off
site and we do not know what client_categories are even possible or what
the codes mean.
You could use the “group_by” method for collections, and select the
first off each group - that saves you doing a query per client, at the
cost of doing one large query and a load of Ruby iteration.Client.all.group_by(&:client_category).map(&:first)
Could you explain the ‘&:’ idiom to me? I cannot seem to find any
examples of it by googling.
At the end of the day, you’re in the realms of doing some fudging to
get the data out you’re after (as you’ve discovered), as you’re not
doing something that’s a natural fit for the DB or ORM. Sorry if
that’s not massively helpful… just letting you know we feel your
pain.
Thanks. Misery shared is misery lessened.
Frederick C. wrote in post #975887:
foo(&:bar) is basically short hand for foo {|f| f.bar}. You’ll
probably get more google hits by searching for Sym to_procFred
Thanks. I indeed did have more success with ‘Sym to proc’. Of course
the bar was set rather low given I found nothing the other way.
BTW I realized my original desire with a little ( actually a great deal
of ) help from the PostgreSQL list. The following works like a champ,
but only for PostgreSQL as far as I know.
cset = Client.select(“DISTINCT ON(client_category) *”)
=> [ . . .cset.size
=> 6
cset[0]
=> #<Client id: 587, dollar_value_total:
#BigDecimal:2b1ebb23a910,‘0.974E3’,9(18), dollar_value_currency_code:
“CAD”, expected_at: “2010-07-19 04:00:00”, expected_to: “133”,
client_category: “0492”, office_code_for_unlading: " ",
service_priority: " ", service_option_identifier: “00257”, . . .,
lock_version: 0>
The DISTINCT ON extension might be of some interest to those working in
PostgreSQl.
On Jan 18, 11:20pm, James B. [email protected] wrote:
Michael P. wrote in post #975879:
You could use the “group_by” method for collections, and select the
first off each group - that saves you doing a query per client, at the
cost of doing one large query and a load of Ruby iteration.Client.all.group_by(&:client_category).map(&:first)
Could you explain the ‘&:’ idiom to me? I cannot seem to find any
examples of it by googling.
foo(&:bar) is basically short hand for foo {|f| f.bar}. You’ll
probably get more google hits by searching for Sym to_proc
Fred
James B. wrote in post #975885:
Could you explain the ‘&:’ idiom to me? I cannot seem to find any
examples of it by googling.
These two posts were helpful for me:
http://jlaine.net/2008/5/8/amp-lified
http://swaggadocio.com/post/287689063
P.S. The example JLaine uses to illustrate implicit conversion is
incorrect, the rest of the article is fine though.
This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.
Sponsor our Newsletter | Privacy Policy | Terms of Service | Remote Ruby Jobs