Hello!
Here is my data model:
== Schema Information
Table name: messages
id :integer(4) not null, primary key
spam :boolean(1) not null
duplicate :boolean(1) not null
ignore :boolean(1) not null
brand_id :integer(4) not null
attitude :string not null
posted_at :datetime not null
Attitude could have 3 states: negative, positive, neutral.
I want to generate report for table, in this way:
Date | Total | Positive | Neutral | Negative
2009-10-10 | 12 | 4 | 7 | 1
(…)
2009-10-30 | 5 | 2 | 1 | 1
And ignore all records which have:
duplicate = true
ignore = true
spam = true
How it’s could be done under activerecord / Rails?
Joao S. wrote:
Hello!
Here is my data model:
== Schema Information
Table name: messages
id :integer(4) not null, primary key
spam :boolean(1) not null
duplicate :boolean(1) not null
ignore :boolean(1) not null
brand_id :integer(4) not null
attitude :string not null
posted_at :datetime not null
Attitude could have 3 states: negative, positive, neutral.
I want to generate report for table, in this way:
Date | Total | Positive | Neutral | Negative
2009-10-10 | 12 | 4 | 7 | 1
(…)
2009-10-30 | 5 | 2 | 1 | 1
And ignore all records which have:
duplicate = true
ignore = true
spam = true
How it’s could be done under activerecord / Rails?
If you only want the attitude to have 3 states, you can add a line not
dissimilar to: “self.attitude[‘Negative’,‘Positive’,‘Neutral’]” to your
message.rb model. Then use a collection select to have only these
options available.
You can use an array method to strip out the keys and values you don’t
want: message.delete_if!{|key,value| key == ‘duplicate’, value ==
‘true’’}. You’ll have to check my syntax for that.
I hope this helps.
Nope! I of course have working application, data model and so on :-).
Now i want to implement reporting functionality so i write this post
:-). Anyone?
Joao S. wrote:
Hello!
Here is my data model:
== Schema Information
Table name: messages
id :integer(4) not null, primary key
spam :boolean(1) not null
duplicate :boolean(1) not null
ignore :boolean(1) not null
brand_id :integer(4) not null
attitude :string not null
posted_at :datetime not null
Attitude could have 3 states: negative, positive, neutral.
I want to generate report for table, in this way:
Date | Total | Positive | Neutral | Negative
2009-10-10 | 12 | 4 | 7 | 1
(…)
2009-10-30 | 5 | 2 | 1 | 1
And ignore all records which have:
duplicate = true
ignore = true
spam = true
How it’s could be done under activerecord / Rails?
Sounds like you want us to create your migrations, your models, your
controllers, and your view.
I’ll get you started with the migrations:
http://railsbrain.com/api/rails-2.3.2/doc/index.html?a=C00000766&name=Migration
The rest of the documentation is here:
http://railsbrain.com/api/rails-2.3.2/doc/index.html
Joao S. wrote:
Of course when i mention ‘table’ in title, i mean ‘resultset’.
You need to learn about the SQL count function and the AR Calculations
module.
Best,
–Â
Marnen Laibow-Koser
http://www.marnen.org
[email protected]
Marnen Laibow-Koser wrote:
Joao S. wrote:
Of course when i mention ‘table’ in title, i mean ‘resultset’.
You need to learn about the SQL count function and the AR Calculations
module.
Best,
–Â
Marnen Laibow-Koser
http://www.marnen.org
[email protected]
I know, i know, but i don’t know how i can start - so i clean
resolution. Groping? Sum()?
Of course when i mention ‘table’ in title, i mean ‘resultset’.
Joao S. wrote:
Marnen Laibow-Koser wrote:
Joao S. wrote:
Of course when i mention ‘table’ in title, i mean ‘resultset’.
You need to learn about the SQL count function and the AR Calculations
module.
Best,
–Â
Marnen Laibow-Koser
http://www.marnen.org
[email protected]
I know, i know, but i don’t know how i can start - so i clean
resolution. Groping? Sum()?
No, not sum(), count(), exactly as I said in my previous message. At
this point, go read the documentation for the things I’ve already
suggest. I’ll help if you have problems, but I won’t feed you code
until you make an attempt of your own.
Best,
–Â
Marnen Laibow-Koser
http://www.marnen.org
[email protected]
Data.all :select => “DATE(posted_at) AS date,
COUNT(*) AS total,
SUM(attitude = ‘positive’) AS positive,
SUM(attitude = ‘neutral’) AS neutral,
SUM(attitude = ‘negative’) AS negative”,
:conditions => [ “duplicate <> ? AND ignore <> ? AND spam <>
?”,
true, true, true ],
:group => :date,
:order => :date
?
It produces this SQL query:
Message Load (0.4ms) SELECT DATE(posted_at) AS date,
COUNT(*) AS total,
SUM(attitude = ‘positive’) AS positive,
SUM(attitude = ‘neutral’) AS neutral,
SUM(attitude = ‘negative’) AS negative FROM messages WHERE
(messages.brand_id = 1) AND (messages.spam = 0 AND
messages.duplicate = 0 AND messages.ignore = 0) GROUP BY date
ORDER BY date, posted_at DESC
Joao S. wrote:
Data.all :select => “DATE(posted_at) AS date,
COUNT(*) AS total,
SUM(attitude = ‘positive’) AS positive,
SUM(attitude = ‘neutral’) AS neutral,
SUM(attitude = ‘negative’) AS negative”,
:conditions => [ “duplicate <> ? AND ignore <> ? AND spam <>
?”,
true, true, true ],
:group => :date,
:order => :date
?
It won’t work:
class Brand < ActiveRecord::Base
has_many :messages, :dependent => :destroy
def report()
self.messages.find(:all, :select => “DATE(posted_at) AS date,
COUNT(*) AS total,
SUM(attitude = ‘positive’) AS positive,
SUM(attitude = ‘neutral’) AS neutral,
SUM(attitude = ‘negative’) AS negative”,
:group => :date,
:order => :date)
end
end
Brand.find(1).report
=> [#, #, #]
