Convert Sequel Dataset to JSON Object

Discussion in 'Ruby' started by Phil Tayo, Feb 17, 2010.

  1. Phil Tayo

    Phil Tayo Guest

    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::Dataset:0x4253fc4>
    2.
    <code>
    Task.filter('id < ?', id).each { |task|
    "{\"task\" : \"#{task.task}\",\"createdat\" :
    \"#{task.created_at}\", \"id\" : \"#{task.id}\" }"
    }
    </code>
    Generates: #<Sequel::SQLite::Dataset: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, Feb 17, 2010
    #1
    1. Advertisements

  2. Phil Tayo

    Phil Tayo Guest

    I also tried:

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

    but this returns: "#<Sequel::SQLite::Dataset:0x4253e48>"
     
    Phil Tayo, Feb 17, 2010
    #2
    1. Advertisements

  3. Phil Tayo

    Phil Tayo Guest

    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
     
    Phil Tayo, Feb 17, 2010
    #3
  4. Phil Tayo

    Jeremy Evans Guest

    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
     
    Jeremy Evans, Feb 18, 2010
    #4
  5. Phil Tayo

    Phil Tayo Guest

    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:
    Can you point me in the direction of any useful reading material?

    Thanks in advance,

    Phil
     
    Phil Tayo, Feb 19, 2010
    #5
  6. Phil Tayo

    Jeremy Evans Guest

    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
     
    Jeremy Evans, Feb 19, 2010
    #6
  7. Phil Tayo

    Phil Tayo Guest

    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.
     
    Phil Tayo, Feb 19, 2010
    #7
  8. Phil Tayo

    Jeremy Evans Guest

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

    Jeremy
     
    Jeremy Evans, Feb 19, 2010
    #8
  9. Phil Tayo

    Phil Tayo Guest

    [{"country":"uk","task":"qwdwd","created_at":{"json_class":"Time","n":407000000,"s":1266543411},"id":3}]
    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".
     
    Phil Tayo, Feb 19, 2010
    #9
  10. Phil Tayo

    Jeremy Evans Guest

    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
     
    Jeremy Evans, Feb 19, 2010
    #10
  11. Phil Tayo

    Phil Tayo Guest

    hehe thanks and yes I am ! :)
     
    Phil Tayo, Feb 19, 2010
    #11
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.