MariaDB views: I want to replace repeated multi-table joins in my queries with a view – are there any issues to watch out for?

Rather than

SELECT a.pk, b.pk, c.pk, d.name  FROM a JOIN b on b.pk = a.fk JOIN c on c.pk = b.fk JOIN d on d.pk = c.fk 

I can do

SELECT a_pk, b_pk, c_pk, d_name  FROM view_a_b_c_d 

I have a lot of this sort of thing through my code.

I’ve done a performance test, and the differences seem to be negligible, and I feel it would greatly tidy my codebase up and remove a lot of repetition.

But before I commit to that (as it’d be a big change with a lot of work and testing), I want to check that this IS a good thing to do. I didn’t study computer science and have no formal DBA training. I’m also a sole dev working on my own closed-source product. So I don’t get much input from the outside world, unless I strike out and ask for it.

Thank you – any opinions/experience appreciated.

How do I handle initiative when a new force joins a combat that’s already in progress?

I am DMing a small hunger-games-like adventure where there are multiple parties of NPC who also are participating. It is pretty likely that the PCs will happen upon some of the NPCs fighting some monsters.

How do I handle the PCs joining the fight, do I roll initiative all over again? Do I just roll for the new combatants and add them to the round?


Also see this similar question for Pathfinder:
How to handle some new NPCs who enter a fight in progress?

How to make multiple right joins in one single command for different child tables?

On the followin image you see 3 tables: A, B , C. A: Is the table that holds the foreign keys from B and C

enter image description here

enter image description here

If I do a right join between A and B on that foreign key, it works, meaning I get the data from column fooB. Same between A and C with fooC.

But If I put both right joins under the same command it fails with null

+-------------+---------------+ | fooB        | fooC          | +-------------+---------------+ | NULL        | abc           | | NULL        | xyz           | 

*This is slightly different as the image with the commands posted below, as this comes straight from the real deployment, however cmds below still show that both columns do not show up.

I’ve tried mixin up left joins, right joins , outer joins all with same result. I have googled about this, but the way my question is formulated is awkard and the hits I get return the traditional right joins for just one table.

How do I get this to work in one command? CMDS Below:

enter image description here

Same commands as above but in text, as comments are asking for text rather than images:

MariaDB [joinPOC]> select fooB from tableA right join tableB on tableB_idtableB=tableB.idtableB; +------+ | fooB | +------+ | b1   | | b2   | | b3   | +------+ 3 rows in set (0.001 sec)  MariaDB [joinPOC]> select fooC from tableA right join tableC on tableC_idtableC=tableC.idtableC; +------+ | fooC | +------+ | c1   | | c2   | | c3   | +------+ 3 rows in set (0.001 sec)  MariaDB [joinPOC]> select fooB from tableA right join tableB on tableB_idtableB=tableB.idtableB right join tableC on tableC_idtableC=tableC.idtableC; +------+ | fooB | +------+ | b1   | | b2   | | b3   | +------+ 

–HERE IS THE ENTIRE SCRIPT–

-- MySQL Workbench Forward Engineering  SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';  -- ----------------------------------------------------- -- Schema joinPOC -- ----------------------------------------------------- DROP SCHEMA IF EXISTS `joinPOC` ;  -- ----------------------------------------------------- -- Schema joinPOC -- ----------------------------------------------------- CREATE SCHEMA IF NOT EXISTS `joinPOC` ; USE `joinPOC` ;  -- ----------------------------------------------------- -- Table `joinPOC`.`tableB` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `joinPOC`.`tableB` (   `idtableB` INT NOT NULL,   `fooB` VARCHAR(10) NOT NULL,   PRIMARY KEY (`idtableB`)) ENGINE = InnoDB;   -- ----------------------------------------------------- -- Table `joinPOC`.`tableC` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `joinPOC`.`tableC` (   `idtableC` INT NOT NULL,   `fooC` VARCHAR(10) NOT NULL,   PRIMARY KEY (`idtableC`)) ENGINE = InnoDB;   -- ----------------------------------------------------- -- Table `joinPOC`.`tableA` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `joinPOC`.`tableA` (   `idtableA` INT NOT NULL,   `fooA` VARCHAR(10) NOT NULL,   `tableB_idtableB` INT NOT NULL,   `tableC_idtableC` INT NOT NULL,   PRIMARY KEY (`idtableA`),   INDEX `fk_tableA_tableB_idx` (`tableB_idtableB` ASC),   INDEX `fk_tableA_tableC1_idx` (`tableC_idtableC` ASC),   CONSTRAINT `fk_tableA_tableB`     FOREIGN KEY (`tableB_idtableB`)     REFERENCES `joinPOC`.`tableB` (`idtableB`)     ON DELETE NO ACTION     ON UPDATE NO ACTION,   CONSTRAINT `fk_tableA_tableC1`     FOREIGN KEY (`tableC_idtableC`)     REFERENCES `joinPOC`.`tableC` (`idtableC`)     ON DELETE NO ACTION     ON UPDATE NO ACTION) ENGINE = InnoDB;   SET SQL_MODE=@OLD_SQL_MODE; SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;  -- ----------------------------------------------------- -- Data for table `joinPOC`.`tableB` -- ----------------------------------------------------- START TRANSACTION; USE `joinPOC`; INSERT INTO `joinPOC`.`tableB` (`idtableB`, `fooB`) VALUES (1, 'b1'); INSERT INTO `joinPOC`.`tableB` (`idtableB`, `fooB`) VALUES (2, 'b2'); INSERT INTO `joinPOC`.`tableB` (`idtableB`, `fooB`) VALUES (3, 'b3');  COMMIT;   -- ----------------------------------------------------- -- Data for table `joinPOC`.`tableC` -- ----------------------------------------------------- START TRANSACTION; USE `joinPOC`; INSERT INTO `joinPOC`.`tableC` (`idtableC`, `fooC`) VALUES (1, 'c1'); INSERT INTO `joinPOC`.`tableC` (`idtableC`, `fooC`) VALUES (2, 'c2'); INSERT INTO `joinPOC`.`tableC` (`idtableC`, `fooC`) VALUES (3, 'c3');  COMMIT;   -- ----------------------------------------------------- -- Data for table `joinPOC`.`tableA` -- ----------------------------------------------------- START TRANSACTION; USE `joinPOC`; INSERT INTO `joinPOC`.`tableA` (`idtableA`, `fooA`, `tableB_idtableB`, `tableC_idtableC`) VALUES (1, 'a1', 1, 1); INSERT INTO `joinPOC`.`tableA` (`idtableA`, `fooA`, `tableB_idtableB`, `tableC_idtableC`) VALUES (2, 'a2', 2, 2); INSERT INTO `joinPOC`.`tableA` (`idtableA`, `fooA`, `tableB_idtableB`, `tableC_idtableC`) VALUES (3, 'a3', 3, 3);  COMMIT; 

Desired output in a single command:

+-------------+---------------+ | fooB        | fooC          | +-------------+---------------+ | b1          | c1            | | b2          | c2            | 

Optimization of a query to retrieve records randomly with multiple joins and filters

I have the following schema:

DB Schema

This question was posted also in StackOverflow, but I want to consult also to specialists more focused on DB administration because the nature of my project. Sorry if this is a mistake

Right now, the table Property hold more than 70K records. I’m developing an update to support more than 500 concurrent sessions. The application will support a map a to make the searches, that’s why GeoLocation declares Coordinate as geography data type. Now we have a big problem, because the response time for some queries (the most important ones) is very slow. I mean, the application has to return around 1000 records at once if there are that quantity of results for the specified parameters.

The parameters are distributed on all the tables of the schema (actually, it’s a portion of the schema). Being Features a table which holds all the principal “characteristics” of the properties (# of bedrooms, # of garages, etc).

With that on mind, the query that is taking so much time right now is the following:

DECLARE @cols NVARCHAR(MAX), @query NVARCHAR(MAX);  DECLARE @properties TABLE(     [ID] INT )  INSERT INTO @properties     SELECT p.[Id]     FROM[Property] p     INNER JOIN[GeoLocation] AS[g]          ON[p].[Id] = [g].[PropertyId]     INNER JOIN[PropertyFeature] AS[pf]          ON[pf].[PropertyId] = [p].[Id]     INNER JOIN[Feature] AS[f]          ON[pf].[FeatureId] = [f].[Id]     WHERE[g].[Address] IS NOT NULL AND(([g].[Address] <> N'') OR[g].[Address] IS NULL)         AND[pf].[FeatureId] IN(             Select ID from feature where featuretype = 1)     GROUP BY p.Id, p.ModificationDate     ORDER BY [p].ModificationDate DESC, newid()     OFFSET 0 ROWS     FETCH NEXT 1000 ROWS ONLY  DECLARE @features TABLE(     [Name] NVARCHAR(80) )  INSERT INTO @features     select Name from feature where FeatureType = 1  CREATE TABLE #temptable (     Id INT,     Url NVARCHAR(200),     Title NVARCHAR(300),     Address NVARCHAR(200),     Domain Tinyint,     Price Real,     Image NVARCHAR(150),      Name NVARCHAR(80),     Value NVARCHAR(150) )  INSERT INTO #temptable SELECT     [t].[Id],      [t].[Url],      [t].[GeneratedTitle] AS[Title],      [t].[Address],      [t].[Domain],      [t].[Price],     (SELECT TOP(1) ISNULL([m].[Resize1200x1200], [m].Resize730x532)      FROM [Multimedia] AS[m]      WHERE [t].[Id] = [m].[PropertyId]         and m.MultimediaType = 1      ORDER BY [m].[Order]) AS[Image],      [t].[Name],      [t].[Value] FROM     (SELECT         [p].[Id],         [p].[Url],         [p].[GeneratedTitle],         [g].[Address],         [p].[Domain],         [pr].[Amount] AS Price,         [p].[ModificationDate],         [f].[Name],         [pf].[Value]     FROM [Property] AS [p]     INNER JOIN [GeoLocation] AS[g]          ON [p].[Id] = [g].[PropertyId]     INNER JOIN [PropertyFeature] AS[pf]          ON [pf].[PropertyId] = [p].[Id]     INNER JOIN [Feature] AS[f]          ON [pf].[FeatureId] = [f].[Id]     INNER JOIN [Operation] AS [o]          ON [p].[Id] = [o].[PropertyId]      INNER JOIN [OperationType] AS [o0]          ON [o].[OperationTypeId] = [o0].[Id]      INNER JOIN [Price] AS [pr]          ON [pr].[OperationId] = [o].[Id]      WHERE p.Id in          (Select Id from @properties)     GROUP BY [p].[Id],               [p].[Url],              [p].[GeneratedTitle],               [g].[Address],              [p].[Domain],               [pr].[Amount],              [p].[ModificationDate],              [f].[Name],              [pf].[Value]) AS[t]     ORDER BY[t].[ModificationDate] DESC  SET @cols = STUFF(                 (                     SELECT DISTINCT                             ','+QUOTENAME(c.[Name])                     FROM @features c FOR XML PATH(''), TYPE                  ).value('.', 'nvarchar(max)'), 1, 1, ''); SET @query = 'SELECT [Id],                       [Url],                       [Title],                       [Address],                       [Domain],                       [Price],                       [Image],                       ' + @cols + '                FROM (SELECT [Id],                              [Url],                              [Title],                              [Address],                              [Domain],                              [Price],                              [Image],                              [Value] AS [value],                              [Name] AS[name]                       FROM #temptable)x                       PIVOT(max(value) for name in ('+@cols+')) p'; EXECUTE(@query);  DROP TABLE #temptable 

The execution plan and Live query statistics show me the following:

Query Execution Plan

The previous query tries to obtain randomly a X number of records IDs, holding all the filter criteria to obtain only the IDs of the records which meet that criteria. The time right now is up to 15 seconds. It’s a lot if we talk about more than 400 users using concurrently the application.

Please, help me with this. I’m three weeks trying to solve this problem with no success, but a lot of advances has been made (before it was consuming 2 minutes in avg).

If it helps, I can give you access to a “dummy” deployed version of the DB with the same quantity of records to test and see directly the problem.

Thanks in advance…

===================================================================================================== INDEXES:

the indexes that are currently on the tables are the following:

GO CREATE UNIQUE NONCLUSTERED INDEX IX_Property_ModificationDate  ON [dbo].[Property] (ModificationDate DESC)  WITH( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, FILLFACTOR = 90, ONLINE = ON)  GO CREATE NONCLUSTERED INDEX [IX_Property_ParentId_StatusCode]  ON [dbo].[Property] ([ParentId] ASC, [StatusCode] ASC) WITH( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, FILLFACTOR = 90, ONLINE = ON);  GO CREATE NONCLUSTERED INDEX [IX_Property_ParentId_StatusCode_Id_ModificationDate]  ON [dbo].[Property] ([ParentId] ASC, [StatusCode] ASC, [Id] ASC, [ModificationDate] ASC) WITH( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, FILLFACTOR = 90, ONLINE = ON);  GO CREATE NONCLUSTERED INDEX [IX_Property_ParentId]     ON [dbo].[Property]([ParentId] ASC)     WITH( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, FILLFACTOR = 90, ONLINE = ON);      GO CREATE NONCLUSTERED INDEX [IX_Property_Identity_Domain_StatusCode]     ON [dbo].[Property]([Identity] ASC, [Domain] ASC, [StatusCode] ASC)     WITH( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, FILLFACTOR = 90, ONLINE = ON);  GO CREATE NONCLUSTERED INDEX [IX_Property_Id_ModificationDate]  ON [dbo].[Property] (Id ASC, ModificationDate ASC) WITH( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, FILLFACTOR = 90, ONLINE = ON);  GO CREATE NONCLUSTERED INDEX [IX_Property_PublisherId]     ON [dbo].[Property]([PublisherId] ASC)     WITH( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, FILLFACTOR = 90, ONLINE = ON);   GO CREATE NONCLUSTERED INDEX [IX_Property_RealEstateTypeId]     ON [dbo].[Property]([RealEstateTypeId] ASC)     WITH( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, FILLFACTOR = 90, ONLINE = ON)   GO  CREATE INDEX FIX_Property_StatusCode_Online ON [dbo].[Property](StatusCode) WHERE StatusCode = 1 WITH( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, FILLFACTOR = 90, ONLINE = ON) GO  CREATE INDEX FIX_Property_StatusCode_Offline ON [dbo].[Property](StatusCode) WHERE StatusCode = 0 WITH( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, FILLFACTOR = 90, ONLINE = ON) GO  CREATE INDEX FIX_Property_Domain_Urbania ON [dbo].[Property](Domain) WHERE Domain = 1 WITH( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, FILLFACTOR = 90, ONLINE = ON) GO  CREATE INDEX FIX_Property_Domain_Adondevivir ON [dbo].[Property](Domain) WHERE Domain = 2 WITH( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, FILLFACTOR = 90, ONLINE = ON) GO  GO CREATE NONCLUSTERED INDEX [IX_GeoLocation_PropertyId_ModificationDate]  ON [dbo].[GeoLocation] (PropertyId ASC, [ModificationDate] DESC) WITH( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, FILLFACTOR = 90, ONLINE = ON);  GO CREATE NONCLUSTERED INDEX [IX_GeoLocation_PropertyId_Address]  ON [dbo].[GeoLocation] (PropertyId ASC, [Address] ASC) WITH( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, FILLFACTOR = 90, ONLINE = ON);  GO CREATE UNIQUE NONCLUSTERED INDEX IX_GeoLocation_ModificationDate  ON [dbo].[GeoLocation] (ModificationDate DESC)  WITH( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, FILLFACTOR = 90, ONLINE = ON) GO  CREATE NONCLUSTERED INDEX [IX_GeoLocation_Ubigeo] ON [dbo].[GeoLocation]([Ubigeo] ASC) WITH( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, FILLFACTOR = 90, ONLINE = ON)  GO CREATE UNIQUE NONCLUSTERED INDEX [IX_GeoLocation_PropertyId]     ON [dbo].[GeoLocation]([PropertyId] ASC)     WITH( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, FILLFACTOR = 90, ONLINE = ON) GO  CREATE SPATIAL INDEX SIX_GeoLocation_Coordinate ON [dbo].[GeoLocation](Coordinate) GO  CREATE INDEX FIX_GeoLocation_Domain_Urbania ON [dbo].[GeoLocation](Domain) WHERE Domain = 1 WITH( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, FILLFACTOR = 90, ONLINE = ON) GO  CREATE INDEX FIX_GeoLocation_Domain_Adondevivir ON [dbo].[GeoLocation](Domain) WHERE Domain = 2 WITH( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, FILLFACTOR = 90, ONLINE = ON) GO  GO CREATE NONCLUSTERED INDEX [IX_Multimedia_PropertyId_Order]  ON [dbo].[Multimedia] (PropertyId ASC, [Order] ASC) WITH( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, FILLFACTOR = 90, ONLINE = ON);  GO CREATE NONCLUSTERED INDEX [IX_Multimedia_PropertyId]     ON [dbo].[Multimedia]([PropertyId] ASC)     WITH( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, FILLFACTOR = 90, ONLINE = ON);  GO CREATE NONCLUSTERED INDEX [IX_Multimedia_Order]     ON [dbo].[Multimedia]([Order] ASC)     WITH( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, FILLFACTOR = 90, ONLINE = ON); GO  CREATE NONCLUSTERED INDEX [PK_Multimedia_Property]     ON [dbo].[Multimedia]([Id] ASC, [PropertyId] ASC); GO  CREATE INDEX FIX_Multimedia_MultimediaType_Image ON [dbo].[Multimedia](MultimediaType) WHERE MultimediaType = 1 WITH( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, FILLFACTOR = 90, ONLINE = ON) GO  GO CREATE NONCLUSTERED INDEX [IX_PropertyFeature_PropertyId_FeatureId]  ON [dbo].[PropertyFeature] (PropertyId ASC, [FeatureId] ASC) WITH( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, FILLFACTOR = 90, ONLINE = ON);  GO CREATE NONCLUSTERED INDEX [IX_PropertyFeature_FeatureId]     ON [dbo].[PropertyFeature]([FeatureId] ASC);   GO CREATE NONCLUSTERED INDEX [IX_PropertyFeature_PropertyId]     ON [dbo].[PropertyFeature]([PropertyId] ASC);   GO CREATE NONCLUSTERED INDEX [IX_PropertyFeature-FeatureId]     ON [dbo].[PropertyFeature]([Id] ASC, [FeatureId] ASC);   GO CREATE NONCLUSTERED INDEX [IX_PropertyFeature_Property]     ON [dbo].[PropertyFeature]([Id] ASC, [PropertyId] ASC);  GO CREATE NONCLUSTERED INDEX [IX_Operation_PropertyId]     ON [dbo].[Operation]([PropertyId] ASC);  GO CREATE NONCLUSTERED INDEX [IX_Operation_OperationTypeId]     ON [dbo].[Operation]([OperationTypeId] ASC);  GO CREATE NONCLUSTERED INDEX [IX_Price_OperationId]     ON [dbo].[Price]([OperationId] ASC);  GO CREATE NONCLUSTERED INDEX [IX_Price_Operation]     ON [dbo].[Price]([Id] ASC, [OperationId] ASC); 

group by with joins

So I have 2 tables with identical structures that represent data from 2 vendors…..I would have set this up in one table but I lost the argument with the data guy…anyway….keep getting an error here:

Column ‘Venor2.DealID’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Trying to simply get an average “MIA” by DealID

Select A1.DealID as A1_DealID,          Avg(A1.Trade_Date_Current_Day_MIA) as Average_MIA,         A2.DealID,          Avg(A2.Trade_Date_Current_Day_MIA) as Average_MIA From  Vendor1_Aggregate_File_Stage as A1 left join Vendor2_Aggregate_File_Stage as A2 on A1.DealID = A2.DealID Group By A1.DealID 

Views, how to construct multiple joins

I have a node activity with fields referencing the node location and a node sport_type. As in, for each activity I need to add a location and a sport type.

On the other hand I also have a node admin with references to location and sport_type. As in, I can create an admin for each sport_type and each location.

How can I create a view which results in the admin when an activity nid is given?

In SQL I would write something like:

SELECT  admin.name FROM    admin JOIN    activity ON activity.location_id = admin.location_id         AND activity.sport_type_id = admin.sport_type_id WHERE   activity.nid = :nid 

I try to create a similar query using the ui of views, where the activity_id would be passed in through the url path. I don’t know how to implement the join though… Any tips? (Using Drupal 7)

Query with a lot of fields and joins

I have to map a legacy database with tables that have a lot of fields and relations. For example (simplified code),

@Entity @Table(name = "VISIT") public class VisitEntity {    @Id    private Integer Id;     @ManyToOne    @JoinColumn(name = "CD_COMPANY")    private CompanyEntity company;     @ManyToOne    @JoinColumn(name = "CD_EMPLOYEE")    private EmployeeEntity employee;     @ManyToOne    @JoinColumn(name = "CD_VISIT_TYPE")    private VisitTypeEntity visitType;     // A lot more fields and relations } 

All other entityes used in the VisitEntity also have a lot of fields and more relations. For example, the EmployeeEntity is mapped as follows:

@Entity @Table(name = "EMPLOYEE") public class EmployeeEntity {    @Id    private Integer Id;     @Column(name = "EMP_NAME")    private String empName;     @ManyToOne    @JoinColumn(name = "CD_DEPARTMENT")    private DepartmentEntity department;     @ManyToOne    @JoinColumn(name = "CD_CITY")    private CityEntity city;     @ManyToOne    @JoinColumn(name = "CD_CATEGORY")    private CategoryEntity category;     // A lot more fields and relations } 

With this classes, a simple find() in the VisitEntity will generate a query with more than 400 columns and more than 40 joins. I’m affraid that, in the future, this will represent a performance problem. I see two ways of reducing this number (assuming that when I use a VisitEntity I only need the field Id and name from the EmployeeEntity:

  1. Insted of using the find method of the entityManager use a TypedQuery referencing only a subset of the filed of the relation tables. For example:
SELECT v, e.id, e.empName, <more subsets from other tables> FROM VisitEntity v LEFT JOIN EmplooyEEEntity e WHERE v.id = :id 

Even using query with a smaller list of fields, I think the JPA will have to create all the main entities.

  1. Use auxiliary entities. For example instead of unsing an EmployeeEntity I’ll create a VisitEmployeeEntity with the fields Id and Name only. This way, the find() method will query only the 2 fields of the EMPLOYEE table.

I appreciate more the second approach, even if it represent that I have to create more small classes.

Since I’m also new to the ORM world, what is your experience/opinon in this matter?

Joins on search criteria

Is thery any way to join other tables to SearchCriteriaInterface, while building it, before calling any getList method of any repository? I mean it should work more or less like joining tables on collections. I haven’t found anything like this so far.

Joining fields on search result when its implemented by collection is not a way to go, of course.