How to Group by on 2 Child Entities and Get Total of Both This Child Entities

How to group by on 2 child entities and get total of both this child entities?

Update

Ok Learning, here it is a solution..

var tot_variants_for_test =
    (from v_name in 
       (from t_op in test 
        select new { v_name = t_op.TestOperation.Select(sv => sv.SubVariants.Variants.Name) }
       ).First().v_name 
     select v_name)
    .Union(
    (from v_name in 
      (from t_opdf in test 
       select new { v_name = t_opdf.TestOperationDifference.Select(sv => sv.SubVariants.Variants.Name) }
     ).First().v_name
     select v_name))
   .Count();

How to calculate over multiple child entities in repository

Well ok I found out how to do. Maybee someone else can use this.

public function findSoldTrades(): array
{
    $qb = $this->createQueryBuilder('t')
        ->innerJoin(Trade::class, 's')
        ->where('s.parent = t.id')
        ->groupBy('t.id')
        ->having('SUM(s.executed) = t.executed')
        ->orderBy('t.created', 'ASC');

    $query = $qb->getQuery();

    return $query->execute();
}

Linq To Entities - how to filter on child entities

I managed to do this by turning the query upside down:

var users = (from user in Users.Include("Group")
             where user.IsEnabled
             select user).ToList().AsQueryable()

from (user in users
      select user.Group).Distinct()

By using the ToList() you force a roundtrip to the database which is required because otherwise the deferred execution comes in the way. The second query only re-orders the retrieved data.

Note: You might not be able to udpate your entities afterwards!

Linq Group by, Concatenate and load child entities on a list

Try the following queries:

1) How to select in a LINQ query a list containg all the orders with the loaded StatusType

var results = (from o in db.Orders
               from s in o.StatusChanges
               select new
                   {
                       OrderId = o.Id,
                       Quantity = o.Quantity,
                       StatusId = s.Id,
                       StatusDate = s.StatusDate,
                       StatusCode = s.StatusType != null ? s.StatusType.Code : null
                   }).ToList();

2) How make a list which contains the Order Id and the Codes of all the status concatenated

var results = (from o in db.Orders
                       select new
                           {
                               o.Id,
                               o.Quantity,
                               StatusCodes =
                           o.StatusChanges.Where(c => c.StatusType != null).Select(c => c.StatusType.Code)
                           }).ToList().Select(x => new
                               {
                                   x.Id,
                                   x.Quantity,
                                   StatusResume = string.Join(",", x.StatusCodes)
                               }).ToList();

SQL query with 1:n relation, find all entities which have two matching children matching

One neat trick is to count the number of unique children such a parent has:

SELECT * 
FROM   parent p
WHERE  EXISTS (SELECT   child_id
               FROM     child c
               WHERE    user IN ('user1', 'user2') AND
                        type IN ('type1', 'type2') AND
                        c.parent_id = p.id
               GROUP BY child_id
               HAVING   COUNT(DISTINCT user) = 2 AND
                        COUNT(DISTINCT type) = 2)

How do I group parent child entities using java 8

What an interesting problem. First I've defined a method for simplicity:

private static JournalDTO toDTO(JournalEntry entry) {
    return new JournalDTO(entry.getId(), entry.getMessage(), new ArrayList<>());
}

Than I defined some small computational Map(s) that would help me search fast:

    Map<Integer, JournalEntry> identity = entries.stream()
            .collect(Collectors.toMap(JournalEntry::getId, Function.identity()));

    Map<Integer, Set<Integer>> map = entries.stream()
            .collect(Collectors.groupingBy(
                    x -> x.getParentId() == null ? -1 : x.getParentId(),
                    Collectors.mapping(JournalEntry::getId, Collectors.toSet())));

The first one should be obvious, it holds an ID paired with a JournalEntry.

The second one holds parentIds to a Set of ids. Basically:

-1 == 1 // -1 meaning it has no parents
 1 == 2 // 1 has a child with id 2
 2 == 3, 4 // 2 has two children with id 3 and 4
 4 == 5, 6 // ... 

If you think about - this is how I find an entire "family" for example (let me know if there are further details required here).

The rest is simple code with a recursive method:

// get those that have no parents first
Set<Integer> ids = map.get(-1);

// this is the ultimate result 
List<JournalDTO> all = new ArrayList<>();

// for each entity with no parents, start searching in the map
ids.forEach(x -> {
     JournalDTO parentDTO = toDTO(identity.get(x));
     recursive(x, map, identity, parentDTO);
     all.add(parentDTO);
}); 

And of course the most important part:

private static void recursive(
        Integer parentId,
        Map<Integer, Set<Integer>> map,
        Map<Integer, JournalEntry> identity,
        JournalDTO journalDTO) {

    Set<Integer> childrenIds = map.get(parentId);

    if (childrenIds != null && !childrenIds.isEmpty()) {
        childrenIds.forEach(x -> {
            JournalDTO childDTO = toDTO(identity.get(x));
            journalDTO.getChildEntries().add(childDTO);
            recursive(x, map, identity, childDTO);
        });
    }
}

I've tested this for a fairly simple case (the one with ==) and seems to work fine for me.

How to select a child entity that is referenced by n parent entities in a ManyToOne relationship?

I would refocus the query as follows, since you have to have as many parameters in the group by as there are properties in the Employee entity:

CriteriaBuilder builder = session.getCriteriaBuilder();
CriteriaQuery<Employee> criteria = builder.createQuery(Employee.class);
Root<Employee> root = criteria.from(Employee.class);

//Subquery to exists
Subquery<Long> numOfTasksSQ= criteria.subquery(Long.class);
Root<Task> rootSQ = numOfTasksSQ.from(Task.class);
Join<Task,Employee> joinSQ = rootSQ.join(Task_.employee,JoinType.INNER);
numOfTasksSQ.select(cb.count(joinSQ.get(Employee_.id)));
numOfTasksSQ.where(cb.equal(joinSQ.get(Employee_.id),root.get(Employee_.id)));

criteria.where(cb.greaterThanOrEqualTo(numOfTasksSQ.getSelection(),MIN_TASK ));

criteria.select(root);

the result would be a query like this:

select e.*
from employee
where (
    (select count(e2.id)
    from task t
    inner join employee e2 on t.employe_id = e2.id
    where e2.id = e.id) > MIN_TASK
);

---

Related Topics