How to handle multiple table query?

[Jerry]

Hi Guys,
I know this is a little out of the broadleaf framework scope but I think you may have some ideas on this.
I want to retrieve all the products that one customer has submitted a review message.
For example, I have submit my review comments to three products. How can I query those products based on the broadleaf framework?

I know the native sql is simple like this:

Code: Select all

select distinct p.* from blc_customer c , blc_rating_detail d , blc_rating_summary s, blc_product p
where c.customer_id = d.customer_id
    and s.rating_summary_id = d.rating_summary_id 
    and p.product_id = s.item_id     
    and c.USER_NAME = 'leeming.jiang@gmail.com'


Can I achieve this by JPQL query like broadleaf do? Any suggestions will be very appreciated.

Regards,
Jerry

[Jerry]

I achieve this by this. This can be working fine.

Code: Select all

@Override
   public List<RatingSummary> readRatingSummaryByCustomerName(String userName, int pageSize) {
      String qlquery = "SELECT ratingSummary FROM org.broadleafcommerce.core.rating.domain.ReviewDetail reviewDetail " +
            "JOIN reviewDetail.customer customer " +
            "JOIN reviewDetail.ratingSummary ratingSummary " +
            "WHERE customer.username = :name";
      
      Query query = em.createQuery(qlquery);
      query.setParameter("name", userName);
      query.setHint(QueryHints.HINT_CACHEABLE, true);
       query.setMaxResults(pageSize);

      return (List<RatingSummary>) query.getResultList();
   }


The problem I've met is :

I create a new file RatingSummay.orm.xml , save it as/site/src/main/resources/config/bc/jpa/domain/RatingSummary.orm.xml
The content:

Code: Select all

<?xml version="1.0" encoding="UTF-8"?>
<entity-mappings xmlns="http://java.sun.com/xml/ns/persistence/orm"
   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
   xsi:schemaLocation="http://java.sun.com/xml/ns/persistence/orm http://java.sun.com/xml/ns/persistence/orm_2_0.xsd" version="2.0">

   <named-query name="TXJJ_READ_RATING_SUMMARY_BY_CUSTOMER_USERNAME">
      <query>
         SELECT ratingSummary
          FROM org.broadleafcommerce.core.rating.domain.ReviewDetail reviewDetail
            JOIN reviewDetail.customer customer
            JOIN reviewDetail.ratingSummary ratingSummary
          WHERE customer.name = :name
      </query>
   </named-query>
   
</entity-mappings>


Modify the file '/site/src/main/webapp/META-INF/persistence.xml' as

Code: Select all

<?xml version="1.0" encoding="UTF-8"?>
<persistence xmlns="http://java.sun.com/xml/ns/persistence"
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd"
             version="2.0">
   <persistence-unit name="blPU" transaction-type="RESOURCE_LOCAL">
      <mapping-file>config/bc/jpa/domain/RatingSummary.orm.xml</mapping-file>
        <class>org.broadleafcommerce.core.rating.domain.RatingSummaryImpl</class>
        <exclude-unlisted-classes/>
    </persistence-unit>
</persistence>




Then I get an error message on the console.
It shows that the "named-query name="TXJJ_READ_RATING_SUMMARY_BY_CUSTOMER_USERNAME"" not found.
I don't know why. So I decided to hard code the jpsql.
Do you have any ideas on this?


Regards,
Jerry

[phillipuniverse]

All of your entities and persistence should be in core. It might be that you have that dao declared in core but the orm.xml file declared in site?

[urfan.alimov]

Hello,

Can somebody provide more details on adding new Admin Section with custom DAOs?

Basically i want to add a couple of admin sections that will query (join) multiple tables for reporting.

Thanks & Regards,