ORM returns wrong number of objects/records

Hi Everyone,
I have a weird one…
When I try to load records by ORM, I get the wrong number of records.

ormCustomers = entityLoad("customer");
writeOutput("Total Number of customers found by ORM : #arrayLen(ormCustomers)#");

qry = new query(
    datasource = "gsn",
    sql = "SELECT * FROM customer"
);

customers = qry.execute().getResult();

writeOutput("Total Number of customers found by query : #customers.recordcount#");

Here’s the output;

Total Number of customers found by ORM : 19
Total Number of customers found by query : 8

If I loop through the array after the ORM entityLoad, and output, say, the DISPLAYNAME property I get multiples;

for (ormCustomer in ormCustomers){
    writeOutput(ormCustomer.getDisplayName() & "<br />");
}

GSN
Demo Customer
Demo Customer
Partner
Partner
Partner
Centricity
Centricity
Centricity
Amazon Marketplace
CustomerFirstBank
CustomerFirstBank
CustomerFirstBank
UPGRADEDEV
UPGRADEDEV
UPGRADEDEV
progleasing
progleasing
progleasing

Upgrading;
to the latest Hibernate beta : 5.4.29.15-BETA
and the latest Lucee RC: 5.3.9.141-RC

Makes no difference.
(I also tried downgrading both - with no difference.)

What on Earth is going on?
I can’t even think of any way to attempt to resolve this either.
I am truly at a loss.

As always - thanks very much!
Gavin.

OS: Windows 10
Java Version: openjdk 11.0.14 2022-01-18 LTS
Tomcat Version: 9.0.46
Lucee Version: 5.3.8.206

can you create a ticket with a test case for this? so we will compare 3.5 with 5.4

Hi
I am happy to create anything you think will help.
However, I’m pretty sure that my work was the test case, already.

In that;

  • I installed 5.3.8.201 - via the Windows installer.
    ** Upgraded to *.206 - straight away - by manually copying in the JAR.

So, my running environment is;

  • The 201 loader
  • The 206 JAR
  • Hibernate is at 3.5.5.77

Did my coding (which utilises the customer class (I did not edit this in anyway)

  • Noticed the weirdness - that this post is about.

Then I did things like;

  • Swapped TO the Maria driver, FROM the MySQL driver (I am using MariaDB)
    ** Still funky
  • Updated Lucee to 5.3.9.141-RC, so I could update Hibernate.
    ** Hibernate 3.5.5.84 (or 5) whatever that version is…
    *** Still funky
    ** Updated Hibernate to 5.4.29.15-BETA
    *** Still funky.

I am NOT convinced there is a Lucee issue.
It could well be something that we are doing.

I am just unsure of what my next steps will be - to even try and determine where the issue is.

We do have a property of “parentId” - that is a many-to-one to the customer, itself - but if that was the issue; the math doesn’t match the results.

Below is the component declaration, properties and init() method. I haven’t included any of the other methods - because they do no come into play here.

  • It does NOT contain a getter that would overwrite the automatic accessors for any of the properties.

Also, I didn’t write this CFC, I mention it not because I am trying to blame someone else - but because it does use some attributes that I have never seen/used before in the “relationship” properties (yes I can RTFM - it is just right now, at the time of writing…) I am not sure if the have any effect on my issue.

  • missingRowIgnored
  • cascadeRefresh

It also extends a component “_mappedSuperClass” - and agan - before working here I have never used this functionality, before - but anyway - it also does NOT contain a method that overwrites property accessors - so I have omitted it from here for brevity.

As always - thanks very much for any help, anyone might be able to suggest.

component persistent="true" cacheuse="nonstrict-read-write" entityname="customer" extends="gsncfc.db._mappedSuperClass"
{
    property name="customerId" column="id" fieldtype="id" generator="native" update="false" setter="false";
    property name="displayName" ormtype="string";
    property name="internalName" ormtype="string" update="false";
    property name="isTenant" ormtype="boolean" default="false";
    property name="isDisabled" ormtype="boolean" default="false";
    property name="created" ormType="timestamp" update="false";
    property name="updated" ormType="timestamp";
    property name="kibanaURL" ormType="string";
    property name="apikey" ormType="string";
    property name="apisecret" ormType="string";
    property name="contactEmail" ormType="string";
    property name="AWSRegion" ormType="string";
    property name="isDemoDataLoading" ormtype="boolean" default="false";
    property name="isBillable" ormType="boolean";
    property name="billingCap" ormType="float";
    property name="surveyCost" ormType="float";
    property name="customerParent" fieldtype="many-to-one" type="any" cfc="gsncfc.db.tenant.customer" singularname="customerParent" fkcolumn="customerParentId" missingRowIgnored="true" lazy="true" cascade="refresh" inverse="true" ;
    property name="licenses" fieldtype="one-to-many" type="array" cfc="gsncfc.db.tenant.customerLicense" singularname="license" fkcolumn="customerId" lazy="extra" fetch="join" cascade="refresh" ;
    property name="products" fieldType="many-to-many" cfc="gsncfc.db.tenant.product" singularName="product" linktable="customerProduct" fkColumn="customerId" inverseJoinColumn="productId" lazy="extra" cascade="refresh" inverse="true";
    property name="roles" fieldType="one-to-many" cfc="gsncfc.db.tenant.role" singularName="role" lazy="true" cascade="refresh";
    property name="datasources" fieldType="one-to-many" type="array" singularName="datasource" cfc="gsncfc.db.metadata.dataSource" fkColumn="customerId" cascade="refresh" missingRowIgnored="false" lazy="true" inverse="true";

    /**
    * @hint A initialization routine, runs when object is created.
    */
    public customer function init() output="false" {
        return this;
    }

To troubleshoot this I would create a second test version of the component that’s stripped back to the absolute minimum and see if the behaviour is the same. If it works ok, then gradually add more of the original until you can replicate the issue.

You could call it customer2 to avoid removing the original and use the table property to point it to the same database table.

component persistent="true" entityname="customer2" table="customer"{
    property name="customerId" column="id" fieldtype="id" generator="native" update="false" setter="false";
    
}
OrmReload();
result = EntityLoad( "customer2" );
dump( result.Len() );
1 Like

Another way to get insight into what might be going on is to look at the SQL statements being run by Hibernate behind the scenes.

You can do this by turning on logging in your orm settings. See this post for more details: ORM log- where and how?

that was my idea too. depending on the db, you can often query a system table to see what SQLs is being run?

1 Like

I generally find it easier to look at the logs on the database side, as I mentioned in that other thread linked by Julian. One issue with ORM logging is it doesn’t necessarily capture bind parameters, so you don’t see the values in the query. The database log does. It’s also easy to run queries on the logs, to get stats, group queries, etc. as they are all there already in a table.

MySQL:
https://dev.mysql.com/doc/refman/8.0/en/query-log.html

SQL Server:

1 Like

I would guess it’s the fetch=“join” on the licenses property, that it’s running a query joining on the license, and that’s why you are getting the duplicates, you are getting a row for each license. Could that be it, does “Demo Customer” have two licenses?