apps/backend/src/modules/menu/application/product-allergen-projection.service.tsTypeScript
import { Injectable } from '@nestjs/common';
import { DatabaseService } from '../../../infrastructure/database/database.service';
@Injectable()
export class ProductAllergenProjectionService {
constructor(private readonly database: DatabaseService) {}
async rebuildProduct(productId: string): Promise<void> {
await this.database.query(
`delete from product_allergen_projection where product_id=$1`,
[productId],
);
await this.database.query(
`with recursive ingredient_tree as (
select
p.id as product_id,
rv.id as source_recipe_version_id,
rc.ingredient_id,
rc.subrecipe_id
from products p
join recipe_versions rv
on rv.recipe_id=p.recipe_id and rv.status='published'
join recipe_components rc on rc.recipe_version_id=rv.id
where p.id=$1
union all
select
it.product_id,
srv.id,
src.ingredient_id,
src.subrecipe_id
from ingredient_tree it
join recipe_versions srv
on srv.recipe_id=it.subrecipe_id and srv.status='published'
join recipe_components src on src.recipe_version_id=srv.id
where it.subrecipe_id is not null
),
ranked as (
select
it.product_id,
ia.allergen_code,
case
when bool_or(ia.relation='contains') then 'contains'
else 'may_contain'
end as relation
from ingredient_tree it
join ingredient_allergens ia on ia.ingredient_id=it.ingredient_id
where it.ingredient_id is not null
group by it.product_id, ia.allergen_code
)
insert into product_allergen_projection(
product_id, allergen_code, relation, source_recipe_version_id
)
select
r.product_id,
r.allergen_code,
r.relation,
(
select source_recipe_version_id
from ingredient_tree it2
join ingredient_allergens ia2 on ia2.ingredient_id=it2.ingredient_id
where it2.product_id=r.product_id
and ia2.allergen_code=r.allergen_code
order by
case ia2.relation when 'contains' then 0 else 1 end,
source_recipe_version_id
limit 1
)
from ranked r`,
[productId],
);
}
}