Ad

Cant Get This Join To Work In Rails

- 1 answer

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.

Ad

Answer

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!

Ad
source: stackoverflow.com
Ad