Convert Sequel Dataset to JSON Object

philthompson · February 17, 2010, 1:07am

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…

philthompson · February 18, 2010, 12:34am

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:

create loops without creating and incrementing ‘i’ by myself
learn the syntax for short if statements

philthompson · February 18, 2010, 5:29pm

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

philthompson · February 17, 2010, 1:30am

I also tried:

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

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

philthompson · February 19, 2010, 1:25am

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:

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

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

philthompson · February 19, 2010, 1:48am

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:

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

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

philthompson · February 19, 2010, 4:31am

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

philthompson · February 19, 2010, 2:44am

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.

philthompson · February 19, 2010, 8:49am

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

philthompson · February 19, 2010, 8:55am

hehe thanks and yes I am !

philthompson · February 19, 2010, 8:34am

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

tedwhite · June 28, 2019, 3:41pm

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