Question 89141 in PostgreSQL Question posted in
The official documentation can be found here.

How to stop ignoring duplicate entries in "WHERE IN ()" query? – Postgresql

I’m trying to get a query that counts the specific macro and microingredients of a meal for a project. I have a List<Long> of ids that represent specific meals, and using that list, I want to calculate combined values of ingredients. The problem is that some of the meals are being duplicated (and that’s OK), but when I pass the list to my SQL query, it ignores the duplicated IDs (for example, if list looks like {1,3,4,1,5} it will only count the ingredients of meal ID 1 once, and I need it to not ignore the second instance.

My query looks like this:

SELECT s.nazwa, SUM(ps.wartosc * (pwp.wartosc / 100)/pwp.liczba_porcji_posilku) as ilosc_skladnika, s.jednostka, s.grupa_skladnikow_odzywczych
FROM produkty_skladniki ps
JOIN posilki_produkty pwp USING (produkt_id)
JOIN skladniki s USING (skladnik_id)
WHERE pwp.posilek_id IN (LIST FROM JAVA HERE)
GROUP BY skladnik_id, s.nazwa, s.jednostka, s.grupa_skladnikow_odzywczych, s.skladnik_id 
ORDER BY s.kolejnosc

And my method that passes the list to

public List<SimulatedMealInfo> checkNorms(List<Menu> menuList){
    //If there are records in DB remove them beforehand
    if(!simulatedMealInfoService.findAll().isEmpty()){
        simulatedMealInfoService.deleteAllCustom();
    }
    //Get the meal ID from each Menu class item
    List<Long> ids = new ArrayList<>();
    for(Menu item : menuList){
        ids.add(item.getMeal().getMealId());
    }
    //Create list of objects returned by query
    List<Object[]> mealObjectView = mealService.checkIngredientsOverall(ids);
    //Create list of objects that will hold needed info
    List<SimulatedMealInfo> mealInfo = new ArrayList<>();
    for(Object[] mealSecond : mealObjectView){
        Double d = (Double) mealSecond[1];
        //Create new objects using info returned by the query
        SimulatedMealInfo simulatedMealInfo = new SimulatedMealInfo(null,mealSecond[0].toString(),
                String.format("%.3f",d/7), mealSecond[2].toString(), mealSecond[3].toString());
        mealInfo.add(simulatedMealInfo);
    }
    //save dump
    simulatedMealInfoService.saveAll(mealInfo);
    return mealInfo;
}
Tags:

2

Answers


  1. 0

    Depending on the database you are using, there is a way to declare a temporary table with some values, make it in join instead of in().
    Example in SQL server of a local table that can be joined:

    SELECT * FROM (VALUES (1), (3), (4), (1), (5)) AS TempTable(ColumnName)
    Login or Signup to reply.
  2. 0

    you can modify your Java code to include duplicated meal IDs:

    public List<SimulatedMealInfo> checkNorms(List<Menu> menuList){
    //If there are records in DB remove them beforehand
    if(!simulatedMealInfoService.findAll().isEmpty()){
        simulatedMealInfoService.deleteAllCustom();
    }
    //Get the meal ID from each Menu class item and duplicate them based on frequency
    List<Long> ids = new ArrayList<>();
    for(Menu item : menuList){
        long mealId = item.getMeal().getMealId();
        int frequency = Collections.frequency(ids, mealId);
        for (int i = 0; i < frequency; i++) {
            ids.add(mealId);
        }
    }
    //Create list of objects returned by query
    List<Object[]> mealObjectView = mealService.checkIngredientsOverall(ids);
    //Create list of objects that will hold needed info
    List<SimulatedMealInfo> mealInfo = new ArrayList<>();
    for(Object[] mealSecond : mealObjectView){
        Double d = (Double) mealSecond[1];
        //Create new objects using info returned by the query
        SimulatedMealInfo simulatedMealInfo = new SimulatedMealInfo(null,mealSecond[0].toString(),
                String.format("%.3f",d/7), mealSecond[2].toString(), mealSecond[3].toString());
        mealInfo.add(simulatedMealInfo);
    }
    //save dump
    simulatedMealInfoService.saveAll(mealInfo);
    return mealInfo;
}

    SQL query that should count the ingredients of each meal ID for each occurrence in the list:

    SELECT s.nazwa, 
       SUM(ps.wartosc * (pwp.wartosc / 100)/pwp.liczba_porcji_posilku) as ilosc_skladnika, 
       s.jednostka, 
       s.grupa_skladnikow_odzywczych
FROM produkty_skladniki ps
JOIN posilki_produkty pwp USING (produkt_id)
JOIN skladniki s USING (skladnik_id)
WHERE pwp.posilek_id IN (?)
GROUP BY pwp.posilek_id, skladnik_id, s.nazwa, s.jednostka, s.grupa_skladnikow_odzywczych, s.skladnik_id 
ORDER BY s.kolejnosc

    GROUP BY clause now includes the pwp.posilek_id column, which ensures that the ingredients are counted separately for each occurrence of a meal ID in the input list. The ? placeholder represents a generic parameter that will be replaced with the list of meal IDs passed from Java.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search