0

I'm trying to write this query:

@myquery = Event.where("reservations.map(&:first)= ?", current_user.id)

but I get this error:

ActionView::Template::Error (PG::SyntaxError: ERROR:  syntax error at or near ":"
LINE 1: ...ECT "trips".* FROM "events" WHERE (reservations.map(&:first)= 1...
                                                                ^

The goal of this query is to find the user ids in the first element of an array of arrays like: [["2","1"],["4","1"]] where 2, 4 are user ids. I tried different solutions but still have this problem!

4
  • Do you want to get any particular records from events table? Commented Nov 17, 2016 at 17:02
  • @dnsh Yes! The records that the current_user.id is found as the first element of the sub-arrays of reservations array. For example, if wanted is 1, the records which has ["1", "..."] in reservations like reservations = [["2","3"], ["1","4"]] Commented Nov 17, 2016 at 17:17
  • @AndreyDeineko I don't think so! Event is a table and I'm using similar queries but not for array of arrays! Commented Nov 17, 2016 at 17:18
  • This is still not clear. Do you just want to filter your array of array depending on current_user.id? You don't need database query for that. Commented Nov 17, 2016 at 17:26

1 Answer 1

0

A little confused but I'm guessing you're trying to create a many-to-many relationship between Users and Events (i.e. a User has many Events and an Event has many Users).

If so you DO NOT want to do it by storing all the user_ids in the Event model and vice versa. What you want to to is create a join table that holds the ids for each

#database table
create_table "user_events", force: :cascade do |t|
  t.integer  "user_id"
  t.integer  "event_id"
  t.datetime "created_at"
  t.datetime "updated_at"
end

#join table
class UserEvents < ActiveRecord::Base
  belongs_to :user
  belongs_to :event
end

#user
class User < ActiveRecored::Base
  has_many :user_events, dependent: :destroy
  has_many :events, through: :user_events
end

#event
class Event < ActiveRecord::Base
  has_many :user_events, dependent: :destroy
  has_many :users, through: :user_events
end

with this structure you are telling your application that Users and Events are related through UserEvents and will have access to all sorts of useful keyword.

For example you could do

Event.includes(:users).all.map {|event| event.user.first.id}

which will return all the id of the first user from every event, which is what I think you're trying to accomplish.

Sign up to request clarification or add additional context in comments.

3 Comments

It's not a many to many relationship. The problem is that I don't know how to work with an array of arrays and search it's first element. To know more, please see another question of mine which is remained unsolved! stackoverflow.com/questions/40614678/…
@AboozarRajabi if the array of arrays is stored in the database you will have to load it into memory to be able to run Ruby code on it. Functions like map are not valid SQL. Can I ask why you're storing an array of arrays in the events model?
@AboozarRajabi I think you are trying to create a many-to-many relationship, but not in the traditional pattern. Would I be correct in saying an Event -> Reservations is one-to-many and Reservations -> Users is many-to-many? (i.e. an Event has many Reservations, a Reservation has one event, a User has many Reservations, and a Reservation has-many Users)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.