Syntax error in rails database query

Question

I have a one to many relationship between two tables (User name: string group: integer) and Relationship (source: integer, target: integer,value: integer). They are related so that userid=relatioship.source.

I am trying to get some json out of it so I have written:

 def self.including_relationships
    User.joins("INNER JOIN relationships ON users.id = relationships.source").select("users.name, users.group, relationships.source, relationships.target, relationships.value").each_with_object(Hash.new{|h, k| h[k] = []}) do |a, obj| 

      obj['nodes'] << a.slice('name','group')
      obj['links'] << a.slice('source', 'target', 'value')

However I get the error

SQLite3::SQLException: near "group": syntax error: SELECT users.name, users.group, relationships.source, relationships.target, relationships.value FROM "users" INNER JOIN relationships ON users.id = relationships.source

when I remove users.group and 'group' in the slice, it works fine.

I have checked my database schema which looks as follows:

ActiveRecord::Schema.define(version: 20150603200530) do

  create_table "relationships", force: true do |t|
    t.integer "source"
    t.integer "target"
    t.integer "value"
  end

  create_table "users", force: true do |t|
    t.string  "name"
    t.integer "group"
  end

end

The tables are all populated as they should be. I can't figure out what the exact syntax error is.

Answer 1

Group is a reserved keyword and you should not use it as your column name.

Basic misconception is people will think Group By collectively as a reserved one. Group and By are two different reserved keywords.

Change the column name group to something else. It will work. Or If you want to use reserved keyword, based on the documentation

If you want to use a keyword as a name, you need to quote it. There are four ways of quoting keywords in SQLite:

'keyword' A keyword in single quotes is a string literal.
"keyword" A keyword in double-quotes is an identifier.
[keyword] A keyword enclosed in square brackets is an identifier. This is not standard SQL. This quoting mechanism is used by MS Access and SQL Server and is included in SQLite for compatibility.
`keyword` A keyword enclosed in grave accents (ASCII code 96) is an identifier. This is not standard SQL. This quoting mechanism is used by MySQL and is included in SQLite for compatibility.

Please refer the list of reserved keywords for sqlite for more information.

Answer 2

group is a reserved word.

To use it as a database object use back ticks around it.

Part of your statements shall be modified as below:

select("users.name, users.`group`, 

Documentation:

• Keywords and Reserved Words
  • Most of the reserved words in the table are forbidden by standard SQL as column or table names (for example, GROUP)