Struggling with Nested Tables and Unit Tests

I am using Spring Boot with Spring Data and attempting to write unit tests.

I have a three tables: Orders have more than one Taco. Tacos have more then one ingredient.

I am attempting to read an Order, with all its Tacos, and the Ingredients for each Taco.

In Order, tacos is a list. In Taco, ingredients is a list.

Here are some good references:
JPQL queries. https://www.objectdb.com/java/jpa/query/jpql/from
EntityGraphs: https://www.radcortez.com/jpa-entity-graphs/
Problems with EntityGraphs: https://stackoverflow.com/questions/31943989/spring-data-jpa-and-namedentitygraphs
JPA LifeCycle: https://www.javabullets.com/jpa-entity-lifecycle/
Baeldung EntityGraph example: https://www.baeldung.com/jpa-entity-graph
Removing old data from cache when testing: https://stackoverflow.com/questions/19057802/junit-hibernate-testing-lazy-associations-fetched
Transactional testing: https://www.marcobehler.com/2014/06/25/should-my-tests-be-transactional 

I have tried many ways to complete the task. The most fruitful was with EntityGraph.

When I attempted the query, I received a Hibernate multiple bag exception.

I researched many articles on the net. One solution is to use a set, but one site cautioned against that, since it must create a Cartesian product of all the tables.
https://vladmihalcea.com/hibernate-multiplebagfetchexception/

I tried to follow the advice, but my example is a little different. I believe the example uses the same approach as nested select statements. I cannot use that idea, since that example is accessing two collections from the same parent table. I want to get all tacos for an order, then get all ingredients for each of those tacos. I cannot perform a join on tacos.ingredients, since tacos is a list. I would have to join on the elements in the list.

I am beginning to think that the best solution is to change to sets instead of lists. Without it, I am forced to do a separate select to get the ingredients for each taco and then add those ingredients to the original result set of the tacos in the order.

Here is a new thought: read all the tacos with their ingredients. Somehow, organize these tacos into orders. Bad idea.

I imagine there is no other way than creating the Cartesian product. I will try with sets.

I attempted to work with sets. The changes were minimal, since many of the methods return Iterable.

I used an EntityGraph with a subgraph for ingredients, but it failed with a null pointer exception.

@NamedEntityGraph(
    name = "Taco_Order.detail",
    attributeNodes = {
        @NamedAttributeNode(value="tacos"
                            ,subgraph="tacos-subgraph"
        )
    }
    ,subgraphs = {
        @NamedSubgraph(
            name="tacos-subgraph",
            attributeNodes= {
                @NamedAttributeNode("ingredients")
            })
    }
    )

If I removed the subgraph, it worked, it even retrieved the ingredients. Confusing.

I removed the entity graph and debugged the code. The tacos were not loaded, as was expected.

I will add the entity graph again and see what happens. It may be a case of stale data in the session. I still got the ingredients. I had added an eager findById to taco, maybe that caused a problem.

I am stuck. I do not know why the ingredients are read.

Observations about CHtml::link and regular links

I am using Yii and have links to several controllers.

I had normal links in the page that worked when I proceeded from the base URL. They failed after I clicked the Home button in the menu.

After clicking Home, the URL was .../index.php/site/index, referencing the site controller.

When I click the normal link,

   

Unit

it tries to access .../index.php/site/Unit and fails.

When I click the CHtml::link in any of these formats,

   

   

   

it tries to access .../index.php/Unit/index and succeeds.

When I click the modified CHtml::link,

   

it tries to access .../index.php/site/Unit/index and fails.

The only one that works properly is the CHtml::link with an array for the destination URL.

The normal URLs add to the existing URL, without noticing that index.php is the actual page and the rest of the URL is path info, so they fail.

Looking at normalizeUrl and createUrl explain the reasons for the CHtml::links.

• normalizeUrl is only called if the parameter is an array
• $this->getId() returns the name of the current controller
• $this->getAction()->getId() returns the name of the action for the current controller
• The remaining items are from createUrl:
• If the route parameters is empty, return the current URL
• If the route does not contain a /, then replace the current action with / and the route
• If the leading character in the route is not / and the current module is not null, then replace the current controller with the module and add the route
• If the route has a site/action or /site, then create the URL from the Yii app, not the current controller

Conclusion, when creating a link for a different controller, use CHtml::link with an array parameter so that the URL is created based on the Yii app and not the current controller.

Yii CActiveRecord Relation to a Relation that is a View

I have tables for Council, Unit, Offices, CouncilOffices, and ViewWithPhonebook. The view is the records for the unit with additional data retrieved from an online phonebook.

CouncilOffices implements a many-to-many relationship between Offices and Council. The council are unit members who hold offices in the organization.

Council Relations

array(
'officeRelation'=>array(self::MANY_MANY, 'Offices', 
                              'council_offices(council_index, offices_index)'
), 
'viewRelation'=>array(self::BELONGS_TO,
            'ViewPhonebook', 'id'),
'unitRelation'=>array(self::BELONGS_TO, 
            'Unit', 'id'),       
);

CouncilOffices Relations

array(
    'councilRelation'=>array(self::BELONGS_TO, 
                             'Council', 'council_index'),
    'officesRelation'=>array(self::BELONGS_TO, 
                             'Offices', 'offices_index'),
);

Offices Relations

array(
     'councilRelation'=>array(self::MANY_MANY, 'Council',
          'council_offices(offices_index, council_index)',
      ),
);

Unit Relations

array(
     'unitRelation'=> array(self::HAS_MANY, 'Council', 'id'),);

ViewWithPhonebook Relations

return array(
     'viewRelation'=> array(self::HAS_MANY, 'Council', 'id'),);

The code worked if I used the unit relation. The problem I had was with the view, since it did not have a primary key. If I tried to include the relation, I received this SQL error.

ON
(`councilRelation`.`id`=`unitRelation`.``) WHERE (year=:year).

It is clear that the generated SQL was looking for a primary key, but couldn't find it. I searched for a long time for a solution. I finally found the solution at http://codeinch.com/yii-set-primary-key-in-model/. Add a primaryKey method to the model and return the name of the primary key.

public function primaryKey() {
    return 'ID';
}

Once this was set, I could retrieve the Council relation from Council Offices and then retrieve the View relation from the Council.

$criteria = new CDbCriteria;
$criteria->condition = 'year=:year';
$criteria->params = array(':year'=>'2020');

$data = CouncilOffices::model()
  ->with(array(
    'councilRelation'
       =>array('select'=>'index, year, id'),        
    'councilRelation.unitRelation'
       =>array('select'=>'ID, Name')))
  ->findAll($criteria);

Mojave VM in Catalina

I just upgraded to Mac OS Catalina and see that my 32-bit applications are no longer supported.

I have decided to create a Mojave Virtual Machine to be able to run the 32-bit applications.

Ironically, my VirtualBox application was 32-bit! I downloaded a new, 64-bit version.

I followed the steps at https://techsviewer.com/install-macos-10-14-mojave-virtualbox-windows/ to create a Mojave ISO image.

The same site has instructions for creating the virtual machine on Windows, but I want it on Mac. The first few steps can be easily modified for Mojave, 64-bit MacOS. Follow the steps until you are asked to edit the settings after the virtual machine is created. They did not work on Mac. I picked up the steps after the settings were changed and the command line actions were done.

I found a separate site that just had me start the vm after it is created. I was prompted for the ISO I created. The process continued to the recovery screen for OS. I clicked Install OS. The Mojave image was found and then I was able to follow the normal installation for Mojave.

Open VirtualBox and click New.

Proved details for virtual machine (VM), then click Continue.

Specify memory size of 2G.

Specify size of hard drive. Suggested 20GB is too small. Trying 100 GB. Click Create.

Review settings and click Start.

 Select ISO image for mojave and click Start.

Select language and click arrow.

Select Disk Utility from the recovery screen.

Select the VBOX HARDDISK and click Erase in order to format the drive.

Create a volume name, choose Mac OS Extended and GUID partition. The GUID is needed for a bootable disk. Click Create.

Drive is created. Click Done.

Quit Disk Utility from the File menu and select Install macOS from the recovery menu.

The mojave.iso file will be used automatically to install OS. Click Continue.

Accept the license by clicking Agree.

Confirm agreement by clicking Agree.

Select the hard drive created earlier and click Install.

Progress is shown.

After a while, the Apple logo will appear.

Something went wrong.

Shut down machine and restarted. Slow progress at the Apple logo. This is the first Apple logo before the recovery menu appears.

Recreated another VM with same parameters. I made it to recovery menu, erased drive, and started install. Reached the second Apple screen. Freezes with 16 minutes remaining.

I found a page for getting stuck at 16 minutes: https://forums.virtualbox.org/viewtopic.php?f=22&t=94495. One recommendation is to set CPUs to 2 and not 4. I had it set for 4 the first time and 1 the second time. Setting it to 2 let me get to the 6 minute mark.

Still waiting... 2 minutes remaining... less than a minute.

Next came a reboot and sucess!

Finished setup, but the desktop is too small.

I installed the VirtualBox extension pack from https://www.virtualbox.org/wiki/Downloads.

In the preferences for virtual box, I set the display scale to 200%. macOS does not have guest extensions. https://forums.virtualbox.org/viewtopic.php?f=8&t=91128

Next was to set up a shared folder. Guest extensions are not available for macOS. Instead, share a folder from Sharing in System Preferences. I followed these instructions to share a folder: https://techsupport.screenplay.com/hc/en-us/articles/360035782711-Creating-a-Shared-Folder-between-your-Mac-running-Catalina-and-a-Virtual-Machine

I created a folder containing my 32-bit apps and shared it.

The important information is the smb URL. That will be used to connect to shared folder from VM. In the VM, open Finder and select Connect to Server from the Go menu.

Enter the smb url.

Select the volumes to mount.

I have a folder containing the 32-bit software that does not run in Catalina, but runs in the Mojave VM.

Some of my apps were so old that they could not run on Mojave! I will abandon them instead of installing another, older macOS VM.