---
# System prepended metadata

title: N+1 queries

---

# N+1 queries

### joins
Carga todos los datos en un sola consulta utilizando INNER JOIN.
Utilizarlo si queremos filtrar datos desde una tabla asociada, pero tener en cuenta que si al resultado comienzo a pedirle datos de la tabla asociada, joins comenzará hacer consultas extras por cada columna de la tabla asociada.

:::success
Good!

 
``` ruby
p = Property.joins(:property_status).where(property_statuses: { name: >'for_sale' })
```
``` sql
Property Load (0.7ms)  SELECT `properties`.* FROM `properties` INNER JOIN >`properties_property_statuses` ON `properties_property_statuses`.`property_id` = `properties`.`id` INNER JOIN >`property_statuses` ON `property_statuses`.`id` = >`properties_property_statuses`.`property_status_id` WHERE >`property_statuses`.`name` = 'for_sale' LIMIT 11
```
:::

:::danger
Bad!

``` ruby
Property.joins(:property_status).each do |property|
  property.statuses.first.name
end
```

``` sql
Property Load (0.6ms)  SELECT `properties`.* FROM `properties` INNER JOIN `properties_property_statuses` ON `properties_property_statuses`.`property_id` = `properties`.`id` INNER JOIN >`property_statuses` ON `property_statuses`.`id` = `properties_property_statuses`.`property_status_id`
PropertyStatus Load (0.6ms)  SELECT `property_statuses`.* FROM `property_statuses` INNER JOIN `properties_property_statuses` ON `property_statuses`.`id` = `properties_property_statuses`.`property_status_id` WHERE `properties_property_statuses`.`property_id` = 1 ORDER BY `property_statuses`.`id` ASC LIMIT 1
PropertyStatus Load (0.7ms)  SELECT `property_statuses`.* FROM `property_statuses` INNER JOIN `properties_property_statuses` ON `property_statuses`.`id` = `properties_property_statuses`.`property_status_id` WHERE `properties_property_statuses`.`property_id` = 2 ORDER BY `property_statuses`.`id` ASC LIMIT 1
```
:::

### preload
Carga todos los datos necesarios en sólo 2 consultas, pero no podemos filtrar por tablas asociadas.

:::success
Good!

``` ruby
Property.preload(:property_status).each do |property|
  property.statuses.first.name
end
```

``` sql
Property Load (0.4ms)  SELECT `properties`.* FROM `properties`
HABTM_PropertyStatus Load (0.5ms)  SELECT `properties_property_statuses`.* FROM `properties_property_statuses` WHERE `properties_property_statuses`.`property_id` IN (1, 2, 3, 4, 5, 6)
PropertyStatus Load (0.5ms)  SELECT `property_statuses`.* FROM `property_statuses` WHERE `property_statuses`.`id` IN (2, 3, 4, 5, 6, 1)
```
:::

:::danger
Bad!
``` ruby
Property.preload(:property_status).where(property_statuses: { name: 'for_sale' })
```

``` sql
Property Load (0.8ms)  SELECT `properties`.* FROM `properties` WHERE `property_statuses`.`name` = 'for_sale' LIMIT 11
Traceback (most recent call last):
ActiveRecord::StatementInvalid (Mysql2::Error: Unknown column 'property_statuses.name' in 'where clause')
```
:::


### eager_load
Carga toda la data en una sóla consulta. Se puede filtrar por campos asociados. Es útil para cuando necesitas utilizar los datos de las tablas asociadas también en el momento de hacer la consulta, pero si no necesitas los datos de las tablas asociadas más que para el filtrado entonces te recomiendo utilizar `includes`. Utiliza LEFT OUTER para hacer la consulta con la tabla asociada.

:::info
``` ruby
Property.eager_load(:property_status).each do |a|
  a.statuses.first.name
end
```

``` sql
SQL (0.9ms)  SELECT `properties`.`id` AS t0_r0, `properties`.`total_area` AS t0_r1, `properties`.`covered_area` AS t0_r2, `properties`.`bedrooms` AS t0_r3, `properties`.`wardrobes` AS t0_r4, `properties`.`bathrooms` AS t0_r5, `properties`.`environments` AS t0_r6, `properties`.`floors` AS t0_r7, `properties`.`antiquity` AS t0_r8, `properties`.`orientation` AS t0_r9, `properties`.`zone` AS t0_r10, `properties`.`street` AS t0_r11, `properties`.`between_streets` AS t0_r12, `properties`.`address_number` AS t0_r13, `properties`.`observations` AS t0_r14, `properties`.`property_type_id` AS t0_r15, `properties`.`created_at` AS t0_r16, `properties`.`updated_at` AS t0_r17, `properties`.`selling_price` AS t0_r18, `properties`.`rent_price` AS t0_r19, `properties`.`city` AS t0_r20, `property_statuses`.`id` AS t1_r0, `property_statuses`.`name` AS t1_r1, `property_statuses`.`created_at` AS t1_r2, `property_statuses`.`updated_at` AS t1_r3 FROM `properties` LEFT OUTER JOIN `properties_property_statuses` ON `properties_property_statuses`.`property_id` = `properties`.`id` LEFT OUTER JOIN `property_statuses` ON `property_statuses`.`id` = `properties_property_statuses`.`property_status_id`
```
:::

### includes
Es la solución para prevenir múltiples consultas al cargar los datos de las tablas asociadas en un única consulta.
`eager_load` carga las columnas en una sola consulta, pero `includes` carga los datos también.
Include trabaja como el `preload` cuando no tenemos en el `where` campos de la tabla asociada, y trabaja como el `eager_load` cuando en el `where` tenemos algún campo de la tabla asociada.

:::info

``` ruby
Property.includes(:property_status).each do |a|
  a.statuses.first.name
end
```

``` sql
Property Load (0.4ms)  SELECT `properties`.* FROM `properties`
HABTM_PropertyStatus Load (0.4ms)  SELECT `properties_property_statuses`.* FROM `properties_property_statuses` WHERE `properties_property_statuses`.`property_id` IN (1, 2, 3, 4, 5, 6)
PropertyStatus Load (0.5ms)  SELECT `property_statuses`.* FROM `property_statuses` WHERE `property_statuses`.`id` IN (2, 3, 4, 5, 6, 1)
```
:::