CODEWITHSUNDEEP
mysqlruby-on-railsruby-on-rails-4join
Rob
Rob

Reputation: 1855

Cant get this join to work in rails

I'm trying to get the following join in rails

SELECT game_catergory_item.item_name, game_category_item_values.key, game_category_item_values.value
FROM game_category_item
INNER JOIN game_category_item_values
ON game_category.id = game_category_item.game_category_id;
WHERE game_category_item.game_gategory_id = 5

Here is my rails coding

def table
  game_id = 5
  @characters = GameCategory.joins(:game_category_items).
  select("game_catergory_items.name, game_category_item_values.key, game_category_item_values.value").
  where('game_category_item.game_gategory_id' => game_id)
end

and here is the output of the above coding

SELECT game_catergory_items.name, game_category_item_values.key, game_category_item_values.value 
FROM "game_categories" 
INNER JOIN "game_category_items" 
ON "game_category_items"."game_category_id" = "game_categories"."id" 
WHERE "game_category_item"."game_gategory_id" = 5

I've been going in circles for the past couple of hours. Its time I asked for help.

EDIT: One step closer after @davejal's help

GameCategoryItem.joins(:game_category_item_values).
select("game_category_items.name, game_category_item_values.key, game_category_item_values.value").
where('game_category_items.game_category_id' => game_id)

gives

SELECT game_category_items.name, game_category_item_values.key, game_category_item_values.value 
FROM "game_category_items" 
INNER JOIN "game_category_item_values" 
ON "game_category_item_values"."game_category_item_id" = "game_category_items"."id" 
WHERE "game_category_items"."game_category_id" = 5

The 'ON' tables and rows are now wrong.

Upvotes: 1

Views: 240

Answers (1)

davejal
davejal

Reputation: 6143

The first query you have should not be able to execute in mysql, the where should be after the join like this:

SELECT game_catergory_item.item_name, game_category_item_values.key, game_category_item_values.value
FROM game_category_item
INNER JOIN game_category_item_values
ON game_category.id = game_category_item.game_category_id
WHERE game_category_item.game_gategory_id = 5;

Looking at your result I would change:

@characters = GameCategory.joins(:game_category_items).

into 

@characters = GameCategoryItem.joins(:game_category_item_values).

Update:

According to your google docs document your query should be:

SELECT game_category_item.item_name, game_category_item_values.key, game_category_item_values.value FROM game_category_item INNER JOIN game_category_item_values ON game_category_item.item_id= game_category_item_values.game_category_item_id WHERE game_category_item.game_gategory_id = 5

Which results to your code to be:

def table
  game_id = 5
  @characters = GameCategoryItem.joins(:game_category_items_values).
  select("game_catergory_items.name, game_category_item_values.key, game_category_item_values.value").
  where('game_category_item.game_category_id' => game_id)
end

Also notice you had game_gategory_id instead of game_category_id in the where clause!

Upvotes: 2

Related Questions