Convert Sequel Dataset to JSON Object

[Phil Tayo]

Hi,

I'm trying to do an AJAX request from my xhtml page to a controller.
The AJAX request passes "id=idgoeshere" where idgoeshere is a number.
Inspecting in Chrome inspector shows me that the id is being passed so
no problems there.

The problem I have is when I try to get my controller to return some
data. I'm using Sequel as my DB and my controller runs a query and is
then supposed to output a json formatted object but I can't get it to
work.

I've tried a few things already:

1.
<code>
Task.filter('id < ?', id).each do |task|
"{\"task\" : \"#{task.task}\",\"createdat\" :
\"#{task.created_at}\", \"id\" : \"#{task.id}\" }"
end
</code>
Generates: #<Sequel::SQLite:ataset:0x4253fc4>
2.
<code>
Task.filter('id < ?', id).each { |task|
"{\"task\" : \"#{task.task}\",\"createdat\" :
\"#{task.created_at}\", \"id\" : \"#{task.id}\" }"
}
</code>
Generates: #<Sequel::SQLite:ataset:0x4253fc4>
I installd the JSON gem and have tried the next two:
3.
<code>
json = JSON.generate(Task.filter('id < ?', id))
json
</code>
Generates error: JSON::GeneratorError: only generation of JSON objects
or arrays allowed
4.
<code>
json = JSON.generate(Task.filter('id < ?', id).to_a)
json
</code>
Generates: ["#<Task:0x42527c8>","#<Task:0x4251814>".....etc]

What I'm trying to return to my xhtml is a JSON object containing all of
my tasks that are returned from the DB, does anyone have any pointers on
how to do it?

Thanks in advance!

Btw, I'm using Ramaze framework but I don't think this is a Ramaze
framework issue so I'm posting it here...

 

[Phil Tayo]

I also tried:

json = Task.filter('id < ?', id).to_json

but this returns: "#<Sequel::SQLite:ataset:0x4253e48>"

 

[Phil Tayo]

well...it's working but it's not pretty:
<code>
def getnew
if request.xhr? and id=request[:id]
tasks = Task.filter('id < ?', id)
json = mda(tasks.count, 3)
i=0
Task.filter('id < ?', id).each do|task|
#create json object
json[0]= "{ \"id\" : \"#{task[:id]}\","
json[1]= "\"created_at\" : \"#{task[:created_at]}\","
json[2]= "\"task\" : \"#{task[:task]}\"}"

#prepend opening bracket if it's the first one
if(i==0)
json[0][0] = "[#{json[0][0]}"
end

#append closing bracket or comma if it's not the last one
if(i==tasks.count-1)
json[3] = "#{json[3]}]"
else
json [3] = "#{json[3]},"
end

i=i+1
end

json
end
end

def mda(width, height)
Array.new(width).map!{ Array.new(height) }
end


</code>

There must be an easier way right?

Things I need to learn how to do:
1. create loops without creating and incrementing 'i' by myself
2. learn the syntax for short if statements

 

[Jeremy Evans]

What I'm trying to return to my xhtml is a JSON object containing all of
my tasks that are returned from the DB, does anyone have any pointers on
how to do it?

FYI, a Sequel dataset is an abstract representation of an SQL query, it
doesn't contain any records itself. Assuming that Array#to_json works
correctly, you could define:

class Sequel:ataset
def to_json
all.to_json
end
end

and then call to_json on a dataset to get the query results in JSON
format.

Jeremy

 

[Phil Tayo]

FYI, a Sequel dataset is an abstract representation of an SQL query, it
doesn't contain any records itself. Assuming that Array#to_json works
correctly, you could define:

class Sequel:ataset
def to_json
all.to_json
end
end

and then call to_json on a dataset to get the query results in JSON
format.

Thanks for that. A couple of problems I still have though:

1. I tried:
...
Task.all.to_json
...
and ".all" still returned an array of object references without the
actual values, like this:

["#<Task:0x42527b4>","#<Task:0x4251800>","#<Task:0x425084c>","#<Task:0x424f898>","#<Task:0x424e8e4>","#<Task:0x424d930>","#<Task:0x424c97c>"]

2. Once that's working, where should I put this code:

class Sequel:ataset
def to_json
all.to_json
end
end

Can you point me in the direction of any useful reading material?

Thanks in advance,

Phil

 

[Jeremy Evans]

FYI, a Sequel dataset is an abstract representation of an SQL query, it
doesn't contain any records itself. Assuming that Array#to_json works
correctly, you could define:

class Sequel:ataset
def to_json
all.to_json
end
end

and then call to_json on a dataset to get the query results in JSON
format.

Thanks for that. A couple of problems I still have though:

1. I tried:
...
Task.all.to_json
...
and ".all" still returned an array of object references without the
actual values, like this:

["#<Task:0x42527b4>","#<Task:0x4251800>","#<Task:0x425084c>","#<Task:0x424f898>","#<Task:0x424e8e4>","#<Task:0x424d930>","#<Task:0x424c97c>"]

2. Once that's working, where should I put this code:

class Sequel:ataset
def to_json
all.to_json
end
end

Can you point me in the direction of any useful reading material?

If your dataset is a model dataset, you probably don't want model
objects. Try this more general version:

class Sequel:ataset
def to_json
naked.all.to_json
end
end

The naked part means that instead of model objects, plain hashes are
returned.

Jeremy

 

[Phil Tayo]

class Sequel:ataset
def to_json
naked.all.to_json
end
end

The naked part means that instead of model objects, plain hashes are
returned.

Jeremy

That's brilliant thanks Jeremy, it's working now.

Now my problem is with the to_json function. I have a created_at field
in the DB of type datetime and the to_json function seems to be
splitting it up and formatting it weirdly, here's the result of my json
object:

[{"country":"uk","task":"qwdwd","created_at":{"json_class":"Time","n":407000000,"s":1266543411},"id":3}]

I'll have to look into this when I have more time.

 

[Jeremy Evans]

Now my problem is with the to_json function. I have a created_at field
in the DB of type datetime and the to_json function seems to be
splitting it up and formatting it weirdly, here's the result of my json
object:

[{"country":"uk","task":"qwdwd","created_at":{"json_class":"Time","n":407000000,"s":1266543411},"id":3}]

I'll have to look into this when I have more time.

You probably want to add Time#to_json to do something, maybe
to_s.to_json or strftime(...).to_json.

Jeremy

 

[Phil Tayo]

[{"country":"uk","task":"qwdwd","created_at":{"json_class":"Time","n":407000000,"s":1266543411},"id":3}]

You probably want to add Time#to_json to do something, maybe
to_s.to_json or strftime(...).to_json.

Jeremy

Thanks but when I do this: Task.naked.all.to_s.to_json it converts the
whole object to one big string. I'm not sure how to specify to to_s for
only one field of my object i.e. "created_at".

 

[Jeremy Evans]

[{"country":"uk","task":"qwdwd","created_at":{"json_class":"Time","n":407000000,"s":1266543411},"id":3}]

You probably want to add Time#to_json to do something, maybe
to_s.to_json or strftime(...).to_json.

Jeremy

Thanks but when I do this: Task.naked.all.to_s.to_json it converts the
whole object to one big string. I'm not sure how to specify to to_s for
only one field of my object i.e. "created_at".

I assume that you are fairly new to ruby, so I'll spell it out for you:

class Time
def to_json
to_s.to_json
end
end
class Sequel:ataset
def to_json
naked.all.to_json
end
end
class Sequel::Model
def self.to_json
dataset.to_json
end
end
Task.to_json

Jeremy

 

[Phil Tayo]

hehe thanks and yes I am !