2

I am using queryDsl for complex search queries in my application. I am new to querydsl. I have started with the below code to fetch few rows from one single table(TableA). But I have to find list of person(Count) with the same id in some other table(TableB)

public static Predicate find(final Long pId)
    QPerson qPerson = QPerson.person;
    Predicate predicate; 
    BooleanBuilder booleanBuilder = new BooleanBuilder();
    if (pId != null) {
        booleanBuilder.or(qPerson.person_no.eq(pId));
    } 
    if (name != null && !name.isEmpty()) {
        booleanBuilder.or(qPerson.expiry_dt.eq(name));
    }
    predicate = booleanBuilder.getValue();   
    return predicate;
    }

TableA:

pId         name

1001      sampleNameA
1002      sampleNameB
1003      sampleNameC

TableB:

pId        name       interests

1001     sampleNameA    music   
1001     sampleNameA    dance
1001     sampleNameA    coding
1003     sampleNameC    music   
1002     sampleNameB    dance
1002     sampleNameB    coding

I need to get output like this with below query

select cnt cnt, tableA.* from master_person_table tableA,(select count(*) cnt from tableB WHERE pId = '1002') cnt WHERE pId = '1002'

OUTPUT:

  count  pId        name       
    2    1002   sampleNameB    

I need to display the no of rows (for id=1002) in my HTML.

Can anyone please help me in finding the count of the pId to be fetched from tableB

Thanks in advance

Java_User
  • 475
  • 2
  • 11
  • 30
  • Can you please paste your output. So that we can help u to build the query. – Pankaj K May 25 '15 at 04:40
  • @Timo Westkämper . I have edited the question. Thanks. – Java_User May 25 '15 at 12:15
  • If we look at table2 then count for pId 1002 for dance is 1 & same for coding. And in output you have count as 2. You want count as 1 or 2. can u please gives us more details so that I can help u out. Regards. – Pankaj K May 26 '15 at 08:36
  • @Pankaj Thanks for your reply. I need to build query with queryDSL. I want the count as 2(actually the count for the id='1002'). My sql query has WHERE condition as id=1002. I need to fetch count based on id column and not based on interest column. I edited the output I want exactly. – Java_User May 26 '15 at 09:20
  • You want ouput for only selected Id or u want it for multiple Ids? – Pankaj K May 26 '15 at 09:36
  • It could be single id or multiple. – Java_User May 27 '15 at 00:59

1 Answers1

0

Please try below query:-

1.For multiple pIds:-

SELECT COUNT(*) AS COUNT, pId, name FROM TableB GROUP BY pId

2.For only selected pId:-

SELECT COUNT(*) AS COUNT, pId, name FROM TableB WHERE pId = 1002

If u need more help plz let me know.

Pankaj K
  • 770
  • 7
  • 20
  • Thanks for the answer. But I need to build query using QueryDSL as I requested. I need to form a queryDSL query for the below one: select cnt cnt, tableA.* from master_person_table tableA,(select count(*) cnt from tableB WHERE pId = '1002') cnt WHERE pId = '1002' – Java_User May 27 '15 at 00:58
  • It could be done using Q generated classes, Predicates or BooleanBuilder. This way I formed a basic query with one table only and it gave the results. But I need help to join multiple tables. In my query I can pass single id or list of ids from the result of TableA to TableB and I just the count of those ids in TableB. Column name(pID) is same in both tables. Can you help me in building a QueryDSL query. – Java_User May 27 '15 at 00:58