Convert Sequel Dataset to JSON Object

#1

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:

Task.filter('id < ?', id).each do |task| "{\"task\" : \"#{task.task}\",\"createdat\" : \"#{task.created_at}\", \"id\" : \"#{task.id}\" }" end Generates: # 2. Task.filter('id < ?', id).each { |task| "{\"task\" : \"#{task.task}\",\"createdat\" : \"#{task.created_at}\", \"id\" : \"#{task.id}\" }" } Generates: # I installd the JSON gem and have tried the next two: 3. json = JSON.generate(Task.filter('id < ?', id)) json Generates error: JSON::GeneratorError: only generation of JSON objects or arrays allowed 4. json = JSON.generate(Task.filter('id < ?', id).to_a) json Generates: ["#","#".....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…

#2

well…it’s working but it’s not pretty:

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[i][0]= “{ “id” : “#{task[:id]}”,”
json[i][1]= ““created_at” : “#{task[:created_at]}”,”
json[i][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[i][3] = "#{json[i][3]}]"
  else
    json [i][3] = "#{json[i][3]},"
  end

i=i+1
end

json

end
end

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

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
#3

Phil T. wrote:

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::Dataset
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

#4

I also tried:

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

but this returns: “#Sequel::SQLite::Dataset:0x4253e48

#5

Jeremy E. wrote:

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::Dataset
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"]

  1. Once that’s working, where should I put this code:

class Sequel::Dataset
def to_json
all.to_json
end
end
Can you point me in the direction of any useful reading material?

Thanks in advance,

Phil

#6

Phil T. wrote:

Jeremy E. wrote:

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::Dataset
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"]

  1. Once that’s working, where should I put this code:

class Sequel::Dataset
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::Dataset
def to_json
naked.all.to_json
end
end

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

Jeremy

#7

Phil T. wrote:

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

#8

class Sequel::Dataset
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.

#9

Phil T. wrote:

[{“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

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::Dataset
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

#10

hehe thanks and yes I am ! :slight_smile:

#11

[{“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

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”.

#12

you can check it out https://www.coolutils.com/XMLViewer its much easy to use and if it satisfies you needs that will be great. Besides that there are many other converters available out there in the market you can try then.
i hope this could help you
All the best