Fetch multiple onetoMany relationships Hibernate JPA

I am using Hibernate JPA 1.0.

I have the following type of model and I consider manyToOne and oneToOne relationships "eagerly" fetched and oneToMany "lazily" fetched.

I want to fetch Entity A and all its associations where a.id=?

• A oneToMany B
  • B oneToOne C
    • C oneToMany D
  • B oneToOne E
    • E oneToMany D
  • B oneToOne F
    • F oneToMany D

Is it possible to load this entity in a single query? Or in a subset of queries baring in mind the "n+1 selects problem"!

So far my solution to loading all of A associations was to perform the following:

"Select DISTINCT a from A a JOIN FETCH a.bs WHERE a.id=:aID"

And then iterate using code in order to fetch all other associations.

Collection B bs = A.getBs();

         for (final B b : bs) {          b.getCs().getDs().size();          b.getEs().getDs().size();          b.getFs().getDs().size();          } 

Obviously there must be a better way of doing this.

3 Answers

Answers 1

You might be interested in reading this article. I have tested both

@Fetch(FetchMode.SUBSELECT) 

and also using a Set instead of List, in combination with fetch = FetchType.EAGER it works.

Answers 2

Use a FETCH JOIN. From the JPA 1.0 specification:

4.4.5.3 Fetch Joins

A FETCH JOIN enables the fetching of an association as a side effect of the execution of a query. A FETCH JOIN is specified over an entity and its related entities.

The syntax for a fetch join is

fetch_join ::= [ LEFT [OUTER] | INNER ] JOIN FETCH join_association_path_expression 

The association referenced by the right side of the FETCH JOIN clause must be an association that belongs to an entity that is returned as a result of the query. It is not permitted to specify an identification variable for the entities referenced by the right side of the FETCH JOIN clause, and hence references to the implicitly fetched entities cannot appear elsewhere in the query.

The following query returns a set of departments. As a side effect, the associated employees for those departments are also retrieved, even though they are not part of the explicit query result. The persistent fields or properties of the employees that are eagerly fetched are fully initialized. The initialization of the relationship properties of the employees that are retrieved is determined by the metadata for the Employee entity class.

SELECT d FROM Department d LEFT JOIN FETCH d.employees WHERE d.deptno = 1 

A fetch join has the same join semantics as the corresponding inner or outer join, except that the related objects specified on the right-hand side of the join operation are not returned in the query result or otherwise referenced in the query. Hence, for example, if department 1 has five employees, the above query returns five references to the department 1 entity.

Of course, use it wisely, don't join too many tables or you will kill performances.

Answers 3

Looking for an answer drawing from credible and/or official sources.

How about JBoss ORM documentation?

https://docs.jboss.org/hibernate/orm/current/userguide/html_single/chapters/fetching/Fetching.html

There are a number of scopes for defining fetching:

static

Static definition of fetching strategies is done in the mappings. The statically-defined fetch strategies is used in the absence of any dynamically defined strategies

SELECT Performs a separate SQL select to load the data. This can either be EAGER (the second select is issued immediately) or LAZY (the second select is delayed until the data is needed). This is the strategy generally termed N+1.

JOIN Inherently an EAGER style of fetching. The data to be fetched is obtained through the use of an SQL outer join.

BATCH Performs a separate SQL select to load a number of related data items using an IN-restriction as part of the SQL WHERE-clause based on a batch size. Again, this can either be EAGER (the second select is issued immediately) or LAZY (the second select is delayed until the data is needed).

SUBSELECT Performs a separate SQL select to load associated data based on the SQL restriction used to load the owner. Again, this can either be EAGER (the second select is issued immediately) or LAZY (the second select is delayed until the data is needed).

---

dynamic (sometimes referred to as runtime)

Dynamic definition is really use-case centric. There are multiple ways to define dynamic fetching:

Fetch profiles defined in mappings, but can be enabled/disabled on the Session.

HQL/JPQL and both Hibernate and JPA Criteria queries have the ability to specify fetching, specific to said query.

Entity Graphs Starting in Hibernate 4.2 (JPA 2.1) this is also an option.

And to prove the answer above, here's an example:

FetchMode.SUBSELECT To demonstrate how FetchMode.SUBSELECT works, we are going to modify the FetchMode.SELECT mapping example to use FetchMode.SUBSELECT:

Example 17. FetchMode.SUBSELECT mapping example:

@OneToMany(mappedBy = "department", fetch = FetchType.LAZY) @Fetch(FetchMode.SUBSELECT) private List<Employee> employees = new ArrayList<>(); 

Now, we are going to fetch all Department entities that match a given
filtering criteria and then navigate their employees collections.

Hibernate is going to avoid the N+1 query issue by generating a single SQL statement to initialize all employees collections for all Department entities that were previously fetched. Instead of using passing all entity identifiers, Hibernate simply reruns the previous query that fetched the Department entities.

Example 18. FetchMode.SUBSELECT mapping example:

List<Department> departments = entityManager.createQuery(     "select d " +     "from Department d " +     "where d.name like :token", Department.class)     .setParameter( "token", "Department%" )     .getResultList();  log.infof( "Fetched %d Departments", departments.size());  for (Department department : departments ) {     assertEquals(3, department.getEmployees().size()); } 

-- Fetched 2 Departments

SELECT     d.id as id1_0_ FROM     Department d where     d.name like 'Department%'  -- Fetched 2 Departments  SELECT     e.department_id as departme3_1_1_,     e.id as id1_1_1_,     e.id as id1_1_0_,     e.department_id as departme3_1_0_,     e.username as username2_1_0_ FROM     Employee e WHERE     e.department_id in (         SELECT             fetchmodes0_.id         FROM             Department fetchmodes0_         WHERE             d.name like 'Department%'     ) 

Read More

Doctrine - map entity for ORM and ODM

I'm working on synchronization entities from one DB to another. I have entities mapped for ORM and ODM, like:

/*  * @ODM\Document(  *     repositoryClass="App\Lib\Repositories\ProductRepository",  *     collection="products"  * )  * @ODM\InheritanceType("COLLECTION_PER_CLASS")  *  * @ORM\Entity(  *     repositoryClass="App\Lib\Repositories\Legacy\LegacyProductRepository"  * )  * @ORM\Table(name="product")  *  * @ORM\HasLifecycleCallbacks()  * @ODM\HasLifecycleCallbacks()  */ class Product extends Article 

It works nice, but I would like to load entity from document manager from mongo db and save it to ORM:

$product = $this->documentManager->find(Product::class, $id); $this->entityManager->merge($product); $this->entityManager->flush(); 

But I have an issue with relations. How do I persist related entity (such as ProductAction) with merging a product?

1 Answers

Answers 1

If I understand correctly, you want to merge "ProductAction" related entities to the ORM when a Product entity is merged to it.

You can use , cascade={"merge"} on the relation., eg.

/**  * @ORM\OneToMany(targetEntity="App\Entity\ProductAction", mappedBy="product", cascade={"persist", "merge"})  */ private $productActions; 

Understanding cascade operations

Merging entities

Read More

Maintain SQL operator precedence when constructing Q objects in Django

I am trying to construct a complex query in Django by adding Q objects based on a list of user inputs:

from django.db.models import Q  q = Q()  expressions = [     {'operator': 'or', 'field': 'f1', 'value': 1},     {'operator': 'or', 'field': 'f2', 'value': 2},     {'operator': 'and', 'field': 'f3', 'value': 3},     {'operator': 'or', 'field': 'f4', 'value': 4}, ]  for item in expressions:     if item['operator'] == 'and':        q.add(Q(**{item['field']:item['value']}), Q.AND )      elif item['operator'] == 'or':        q.add(Q(**{item['field']:item['value']}), Q.OR ) 

Based on this I am expecting to get a query with the following where condition:

f1 = 1 or f2 = 2 and f3 = 3 or f4 = 4 

which, based on the default operator precedence will be executed as

f1 = 1 or (f2 = 2 and f3 = 3) or f4 = 4 

however, I am getting the following query:

((f1 = 1 or f2 = 2) and f3 = 3) or f4 = 4 

It looks like the Q() object forces the conditions to be evaluated in the order they were added.

Is there a way that I can keep the default SQL precedence? Basically I want to tell the ORM not to add parenthesis in my conditions.

2 Answers

Answers 1

Since SQL precedence is the same as Python precedence when it comes to AND, OR, and NOT, you should be able to achieve what you want by letting Python parse the expression.

One quick-and-dirty way to do it would be to construct the expression as a string and let Python eval() it.

from functools import reduce  ops = ["&" if item["operator"] == "and" else "|" for item in expressions] qs = [Q(**{item["field"]: item["value"]}) for item in expressions]  q_string = reduce(     lambda acc, index: acc + " {op} qs[{index}]".format(op=ops[index], index=index),     range(len(expressions)),     "Q()" ) # equals "Q() | qs[0] | qs[1] & qs[2] | qs[3]"  q_expression = eval(q_string) 

Python will parse this expression according to its own operator precedence, and the resulting SQL clause will match your expectations:

f1 = 1 or (f2 = 2 and f3 = 3) or f4 = 4 

Of course, using eval() with user-supplied strings would be a major security risk, so here I'm constructing the Q objects separately (in the same way you did) and just referring to them in the eval string. So I don't think there are any additional security implications of using eval() here.

Answers 2

Seems that you are not the only one with a similar problem. (edited due to @hynekcer 's comment)

A workaround would be to "parse" the incoming parameters into a list of Q() objects and create your query from that list:

from operator import or_ from django.db.models import Q  query_list = []  for item in expressions:     if item['operator'] == 'and' and query_list:         # query_list must have at least one item for this to work         query_list[-1] = query_list[-1] & Q(**{item['field']:item['value']})     elif item['operator'] == 'or':         query_list.append(Q(**{item['field']:item['value']}))     else:         # If you find yourself here, something went wrong... 

Now the query_list contains the individual queries as Q() or the Q() AND Q() relationships between them.
The list can be reduce()d with the or_ operator to create the remaining OR relationships and used in a filter(), get() etc. query:

MyModel.objects.filter(reduce(or_, query_list)) 

---

PS: Although Kevin's answer is clever, using eval() is considered a bad practice and should be used only if completely necessary.

Read More

Implementing a “soft delete” system using sqlalchemy

We are creating a service for an app using tornado and sqlalchemy. The application is written in django and uses a "soft delete mechanism". What that means is that there was no deletion in the underlying mysql tables. To mark a row as deleted we simply set the attributed "delete" as True. However, in the service we are using sqlalchemy. Initially, we started to add check for delete in the queries made through sqlalchemy itself like:

customers = db.query(Customer).filter(not_(Customer.deleted)).all() 

However this leads to a lot of potential bugs because developers tend to miss the check for deleted in there queries. Hence we decided to override the default querying with our query class that does a "pre-filter":

class SafeDeleteMixin(Query):     def __iter__(self):         return Query.__iter__(self.deleted_filter())     def from_self(self, *ent):         # override from_self() to automatically apply         # the criterion too.   this works with count() and         # others.         return Query.from_self(self.deleted_filter(), *ent)     def deleted_filter(self):         mzero = self._mapper_zero()         if mzero is not None:             crit = mzero.class_.deleted == False             return self.enable_assertions(False).filter(crit)         else:             return self 

This inspired from a solution on sqlalchemy docs here:

https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/PreFilteredQuery 

However, we are still facing issues, like in cases where we are doing filter and update together and using this query class as defined above the update does not respect the criterion of delete=False when applying the filter for update.

db = CustomSession(with_deleted=False)() result = db.query(Customer).filter(Customer.id == customer_id).update({Customer.last_active_time: last_active_time }) 

How can I implement the "soft-delete" feature in sqlalchemy

2 Answers

Answers 1

I've done something similar here. We did it a bit differently, we made a service layer that all database access goes through, kind of like a controller, but only for db access, we called it a ResourceManager, and it's heavily inspired by "Domain Driven Design" (great book, invaluable for using SQLAlchemy well). A derived ResourceManager exists for each aggregate root, ie. each resource class you want to get at things through. (Though sometimes for really simple ResourceManagers, the derived manager class itself is generated dynamically) It has a method that gives out your base query, and that base query gets filtered for your soft delete before it's handed out. From then on, you can add to that query generatively for filtering, and finally call it with query.one() or first() or all() or count(). Note, there is one gotcha I encountered for this kind of generative query handling, you can hang yourself if you join a table too many times. In some cases for filtering we had to keep track of which tables had already been joined. If your delete filter is off the primary table, just filter that first, and you can join willy nilly after that.

so something like this:

class ResourceManager(object):      # these will get filled in by the derived class      # you could use ABC tools if you want, we don't bother      model_class = None      serializer_class = None       # the resource manager gets instantiated once per request      # and passed the current requests SQAlchemy session       def __init__(self, dbsession):          self.dbs = dbsession        # hand out base query, assumes we have a boolean 'deleted' column      @property      def query(self):          return self.dbs(self.model_class).filter(             getattr(self.model_class, 'deleted')==False)   class UserManager(ResourceManager):      model_class = User   # some client code might look this  dbs = SomeSessionFactoryIHave()  user_manager = UserManager(dbs)     users = user_manager.query.filter_by(name_last="Duncan").first()         

Now as long as I always start off by going through a ResourceManager, which has other benefits too (see aforementioned book), I know my query is pre-filtered. This has worked very well for us on a current project that has soft-delete and quite an extensive and thorny db schema.

hth!

Answers 2

I would create a function

def customer_query():     return db.session.query(Customer).filter(Customer.deleted == False) 

I used query functions to not forget default flags, to set flags based on user permission, filter using joins etc, so that these things wont be copy-pasted and forgotten at various places.

Read More

How Do I Use Generic Variables for Customer Specific Data

I've created a generic dll that holds commonly used variables. I have user defined fields that are place holders so we can hold customer specific data. This dll will be used in client specific apps.

How can I map these generic variables to the relevant sql table fields so that we can manipulate the custom database? I want to avoid writing custom queries.

Would an ORM like dapper be useful here?

Edit: Per danihp's reponse, I've started looking into Entity frame work. It looks promising. I'm inferring that using Fluent API I can make this dll portable into unique apps and pass a db object (instead of my class?) to do business logic.

Public Class Runs     Private _RunMailPeices As Dictionary(Of String, MailPiece) = New Dictionary(Of String, MailPiece)     Private _run As Integer         Private MailDate As DateTime     Public Property RunMailPeices As Dictionary(Of String, MailPiece)         Get             RunMailPeices = _RunMailPeices         End Get         Set(value As Dictionary(Of String, MailPiece))             _RunMailPeices = value         End Set     End Property      Public Property run As Integer         Get             run = _run         End Get         Set(value As Integer)             _run = value         End Set     End Property End Class 

And:

Public Class MailPiece      Private _address1 As String = String.Empty     Private _address2 As String = String.Empty     Private _string1 As String = String.Empty     Private _string2 As String = String.Empty     Public Property Address1 As String         Get             Address1 = _address1         End Get         Set(value As String)             _address1 = value         End Set     End Property      Public Property Address2 As String         Get             Address2 = _address2         End Get         Set(value As String)             _address2 = value         End Set     End Property     Public Property String1 As String         Get             String1 = _string1         End Get         Set(value As String)             _string1 = value         End Set     End Property      Public Property String2 As String         Get             String2 = _string2         End Get         Set(value As String)             _string2 = value         End Set     End Property End Class 

1 Answers

Answers 1

You are looking for entity framework. You can easily map your classes to tables. You have just 2 classes related between them. Is so easy to map this classes to tables with entity framework. Then you will avoid to write queries, just LINQ expression and navigation though tables with navigation properties.

Entity Framework (EF) is an object-relational mapper that enables .NET developers to work with relational data using domain-specific objects. It eliminates the need for most of the data-access code that developers usually need to write.

It is usual to create a Data Access Layer. "Entity Framework is Microsoft’s recommended data access technology for new applications"

Your code looks easy to be handled by EF, but, if not, you can write new classes to easily persist your custom classes.

You can learn by example EF VB.NET code at Entity Framework Fluent API with VB.NET

Read More

Multiple object types references in Django

We are currently running with the following configuration to avoid other issues.
So for the question: let's assume that this is a must and we can not change the Models part.

At the beginning we had the following models:

class A(Model):     b = ForeignKey(B)     ... set of fields ...  class B(Model):     ... 

Then we added something like this:

class AVer2(Model):     b = ForeignKey(B)     ... ANOTHER set of fields ... 

Assuming an object of type B can only be referenced by either A or AVer2 but never both:

Is there a way to run a query on B that will return, at runtime, the correct object type that references it, in the query result (and the query has both types in it)?

You can assume that an object of type B holds the information regarding who's referencing it.

I am trying to avoid costly whole-system code changes for this.

EDIT: Apparently, my question was not clear. So I will try to explain it better. The answers I got were great but apparently I missed a key point in my question so here it is. Assuming I have the model B from above, and I get some objects:

b_filter = B.objects.filter(some_of_them_have_this_true=True) 

Now, I want to get a field that is in both A and AVer2 with one filter into one values list. So for example, I want to get a field named "MyVal" (both A and AVer2 have it) I don't care what is the actual type. So I want to write something like:

b_filter.values(['a__myval', 'aver2__myval']) 

and get something like the following in return: [{'myval': }] Instead, I currently get [{'a__myval': , 'aver2__myval': None}]

I hope it is clearer.

Thanks!

3 Answers

Answers 1

Assuming that you are getting some reference to a B type object at the time of the request (id, pk, name or anything unique for that matter), you can find if it is referenced by A or AVer2 by trailing backwards it's relationships:

Let the B model have a unique id.
If you want to find whether an object of type B with the id=some_id has been referenced by an object (or objects) of type A or type AVer2, you can:

b = B.objects.get(id=some_id)  if b.a_set.count() > 0:     print ("Referenced by A model") elif b.aver2_set.count() > 0:     print ("Referenced by AVer2 model") else:     print ("Not referenced yet") 

Good luck :)

Answers 2

I'm not sure what do you want to get in query set.

I assumed that you want set of "correct object types" that "has both types in it", so in fact you want set of related class types (like [<class 'main.models.A'>, <class 'main.models.A2'>]). If that is not the case, I can change answer after more specific details in comments.

This is solution for that "class list", you can use it to get what you precisely want.

# Our custom QuerySet that with function that returns list of classes related to given B objects class CustomQuerySet(models.QuerySet):     def get_types(self, *args, **kwargs):         all_queryset = self.all()         return [b.get_a() for b in all_queryset]  # Our custom manager - we make sure we get CustomQuerySet, not QuerySet class TypesManager(models.Manager):     def get_queryset(self, *args, **kwargs):         return CustomQuerySet(self.model)   class B(models.Model):     # some fields      # Managers     objects = models.Manager()     a_types_objects = TypesManager()      # Get proper A "type"     def get_a(self):         if self.a_set.all() and self.a2_set.all():             raise Exception('B object is related to A and A2 at the same time!')         elif self.a_set.all():             return A         elif self.a2_set.all():             return A2         return None   class A(models.Model):     b = models.ForeignKey(         B     )   class A2(models.Model):     b = models.ForeignKey(         B     ) 

And now you can use it like this:

>>> from main.models import * >>> B.a_types_objects.all() <CustomQuerySet [<B: B object>, <B: B object>]> >>> B.a_types_objects.all().get_types() [<class 'main.models.A'>, <class 'main.models.A2'>] >>> B.a_types_objects.filter(id=1) <CustomQuerySet [<B: B object>]> >>> B.a_types_objects.filter(id=1).get_types() [<class 'main.models.A'>] 

Using a_types_objects works like normal objects, but it returns CustomQuerySet, which has extra function returning list of class.

EDIT:

If you worrying about changing a lot of B.objects.(...) into B.a_types_objects.(...) you could just set your main manager to TypesManager like that:

class B(models.Model):     # some fields      # Override manager     objects = TypesManager() 

Rest of your code will remain intact, but from now on you will use CustomQuerySet instead of QuerySet - still, nothing really changes.

Answers 3

Short answer: You can not make your exact need.

Long answer: The first thing that came to my mind when I read your question is Content Types, Generic Foreign Keys and Generic Relations

Whether you will use "normal" foreign keys or "generic foreign keys" (combined with Generic Relation), Your B instances will have both A field and AVer2 field and this natural thing make life easier and make your goal (B instance has a single Field that may be A or Avr2) unreachable. And here you should also override the B model save method to force it to have only the A field and the Avr2 to be None or A to be None and Avr2 to be used. And if you do so, don't forget to add null=True, blank=True to A and Avr2 foreign key fields.

On the other hand, the opposite of your schema makes your goal reachable: B model references A and Avr2 that means that B model has ONE generic foreign key to both A and Avr2 like this: (this code is with Django 1.8, for Django 1.9 or higher the import of GenericRelation, GenericForeignKey has changed)

from django.db import models from django.contrib.contenttypes.generic import GenericRelation, GenericForeignKey from django.contrib.contenttypes.models import ContentType   class B(models.Model):     # Some of your fields here...     content_type = models.ForeignKey(ContentType, null=True, blank=True)     object_id = models.PositiveIntegerField(null=True, blank=True)     # Generic relational field will be associed to diffrent models like A or Avr2     content_object = GenericForeignKey('content_type', 'object_id')   class A(models.Model):     # Some of your fields here...     the_common_field = models.BooleanField()     bbb = GenericRelation(B, related_query_name="a")  # since it is a foreign key, this may be one or many objects refernced (One-To-Many)   class Avr2(models.Model):     # Some of your fields here...     the_common_field = models.BooleanField()     bbb = GenericRelation(B, related_query_name="avr2")  # since it is a foreign key, this may be one or many objects refernced (One-To-Many) 

Now both A and Avr2 have "bbb" field which is a B instance.

a = A(some fields initializations) a.save() b = B(some fields initializations) b.save() a.bbb = [b] a.save() 

Now you can do a.bbb and you get the B instances

And get the A or Avr2 out of b like this:

b.content_object  # which will return an `A object` or an `Avr2 object` 

Now let's return to your goals:

• Is there a way to run a query on B that will return, at runtime, the correct object type that references it, in the query result (and the query has both types in it)?

Yes: like this:

B.objects.get(id=1).content_type  # will return A or Avr2 

• You wanna perform something like this: b_filter = B.objects.filter(some_of_them_have_this_true=True) :

  from django.db.models import Q

  filter = Q(a__common_field=True) | Q(avr2__common_field=True)

  B.objects.filter(filter)

• Getting [{'a__myval': , 'aver2__myval': None}] is 100% normal since values is asked to provide two fields values. One way to overcome this, is by getting two clean queries and then chain them together like so:

  from itertools import chain

  c1 = B.objects.filter(content_type__model='a').values('a__common_field')

  c2 = B.objects.filter(content_type__model='avr2').values('avr2__common_field')

  result_list = list(chain(c1, c2)) 

Please notice that when we added related_query_name to the generic relation, a and avr2 has become accessible from B instances, which is not the default case.

And voilà ! I hope this helps !

Read More

Cakephp 3 ORM saving multiple _joinData using patchEntity

SupplierSchemasTable

$this->hasMany('SupplierSchemaItems');  $this->belongsToMany('Suppliers');  +----+----------+ | id |  title   | +----+----------+ |  1 | schema_1 | |  2 | schema_2 | +----+----------+ 

SuppliersTable

$this->belongsToMany('SupplierSchemas');  $this->belongsToMany('SupplierSchemaItems', [     'through' => 'SupplierSchemaItemsSuppliers',     ]);  +----+------------+ | id |    name    | +----+------------+ |  1 | supplier_1 | +----+------------+ 

SupplierSchemasInputTypesTable

$this->hasMany('SupplierSchemaItems');   +----+----------+ | id |  title   | +----+----------+ |  1 | Text     | |  2 | Textarea | |  3 | Select   | +----+----------+ 

SupplierSchemaItemsTable

$this->belongsTo('SupplierSchemas');  $this->belongsToMany('Suppliers', [         'through' => 'SupplierSchemaItemsSuppliers',     ]);  +----+--------------------------+--------------------+--------------------------------+ | id |          title           | supplier_schema_id | supplier_schemas_input_type_id | +----+--------------------------+--------------------+--------------------------------+ |  1 | Partners                 |                  1 |                              1 | |  2 | Bio                      |                  1 |                              2 | |  3 | Identification Documents |                  1 |                              3 | +----+--------------------------+--------------------+--------------------------------+ 

SupplierSchemaItemsSuppliersTable

$this->belongsTo('SupplierSchemasInputTypes'); $this->belongsTo('SupplierSchemaItems'); $this->belongsTo('Suppliers');  +----+-------------------------+-------------+-------------------------+ | id | supplier_schema_item_id | supplier_id |          value          | +----+-------------------------+-------------+-------------------------+ |  1 |                       1 |           1 | 4                       | |  2 |                       2 |           1 | Supplier Bio Text       | |  3 |                       3 |           1 | Current Signed Passport | |  4 |                       3 |           1 | Driving Licence         | +----+-------------------------+-------------+-------------------------+ 

I need to allow admin to be able to update data in SupplierSchemaItemsSuppliersTable. However when I try to do so using below

$SuppliersTable->patchEntity($supplier, $this->request->data(), [                     'associated' => [  'SupplierSchemas.SupplierSchemaItems.Suppliers',                     ]                 ]); 

It works for rows where both supplier_schema_item_id and supplier_id is different. However for the select ( Identification Documents ) which can have more than one item it fails i.e. will only update the first record and delete the second one.

Below is the Data Dump for the request data:

[     (int) 0 => [         'id' => '1',         'supplier_schema_items' => [             (int) 2 => [                 'id' => '3',                 'suppliers' => [                     (int) 0 => [                         'id' => '1',                         '_joinData' => [                             'id' => '3',                             'value' => 'Current Signed Passport - Edit Test'                         ]                     ],                     (int) 1 => [                         'id' => '1',                         '_joinData' => [                             'id' => '4',                             'value' => 'Driving Licesnce - Edit Test 2'                         ]                     ]                 ]             ]         ]     ] ] 

0 Answers

Read More

How to instruct SQLAlchemy ORM to execute multiple queries in parallel when loading relationships?

I am using SQLAlchemy's ORM. I have a model that has multiple many-to-many relationships:

User User <--MxN--> Organization User <--MxN--> School User <--MxN--> Credentials 

I am implementing these using association tables, so there are also User_to_Organization, User_to_School and User_to_Credentials tables that I don't directly use.

Now, when I attempt to load a single User (using its PK identifier) and its relationships (and related models) using joined eager loading, I get horrible performance (15+ seconds). I assume this is due to this issue:

When multiple levels of depth are used with joined or subquery loading, loading collections-within- collections will multiply the total number of rows fetched in a cartesian fashion. Both forms of eager loading always join from the original parent class.

If I introduce another level or two to the hierarchy:

Organization <--1xN--> Project School <--1xN--> Course Project <--MxN--> Credentials Course <--MxN--> Credentials 

The query takes 50+ seconds to complete, even though the total amount of records in each table is fairly small.

Using lazy loading, I am required to manually load each relationship, and there are multiple round trips to the server.

e.g. Operations, executed serially as queries:

• Get user
• Get user's Organizations
• Get user's Schools
• Get user's credentials
• For each Organization, get its Projects
• For each School, get its Courses
• For each Project, get its Credentials
• For each Course, get its Credentials

Still, it all finishes in less than 200ms.

I was wondering if there is anyway to indeed use lazy loading, but perform the relationship loading queries in parallel. For example, using the concurrent module, asyncio or by using gevent.

e.g. Step 1 (in parallel):

• Get user
• Get user's Organizations
• Get user's Schools
• Get user's credentials

Step 2 (in parallel):

• For each Organization, get its Projects
• For each School, get its Courses

Step 3 (in parallel):

• For each Project, get its Credentials
• For each Course, get its Credentials

Actually, at this point, making a subquery type load can also work, that is, return Organization and OrganizationID/Project/Credentials in two separate queries:

e.g. Step 1 (in parallel):

• Get user
• Get user's Organizations
• Get user's Schools
• Get user's credentials

Step 2 (in parallel):

• Get Organizations
• Get Schools
• Get the Organizations' Projects, join with Credentials
• Get the Schools' Courses, join with Credentials

2 Answers

Answers 1

The first thing you're going to want to do is check to see what queries are actually being executed on the db. I wouldn't assume that SQLAlchemy is doing what you expect unless you're very familiar with it. You can use echo=True on your engine configuration or look at some db logs (not sure how to do that with mysql).

You've mentioned that you're using different loading strategies so I guess you've read through the docs on that ( http://docs.sqlalchemy.org/en/latest/orm/loading_relationships.html). For what you're doing, I'd probably recommend subquery load, but it totally depends on the number of rows / columns you're dealing with. In my experience it's a good general starting point though.

One thing to note, you might need to something like:

db.query(Thing).options(subqueryload('A').subqueryload('B')).filter(Thing.id==x).first()

With filter.first rather that get, as the latter case won't re-execute queries according to your loading strategy if the primary object is already in the identity map.

Finally, I don't know your data - but those numbers sound pretty abysmal for anything short of a huge data set. Check that you have the correct indexes specified on all your tables.

You may have already been through all of this, but based on the information you've provided, it sounds like you need to do more work to narrow down your issue. Is it the db schema, or is it the queries SQLA is executing?

Either way, I'd say, "no" to running multiple queries on different connections. Any attempt to do that could result in inconsistent data coming back to your app, and if you think you've got issues now..... :-)

Answers 2

MySQL has no parallelism in a single connection. For the ORM to do such would require multiple connections to MySQL. Generally, the overhead of trying to do such is "not worth it".

To get a user, his Organizations, Schools, etc, can all be done (in mysql) via a single query:

SELECT user, organization, ...     FROM Users     JOIN Organizations ON ...     etc. 

This is significantly more efficient than

SELECT user FROM ...; SELECT organization ... WHERE user = ...; etc. 

(This is not "parallelism".)

Or maybe your "steps" are not quite 'right'?...

SELECT user, organization, project     FROM Users     JOIN Organizations ...     JOIN Projects ... 

That gets, in a single step, all users, together with all their organizations and projects.

But is a "user" associated with a "project"? If not, then this is the wrong approach.

If the ORM is not providing a mechanism to generate queries like those, than it is "getting in the way".

Read More

How to create an application supporting multiple databases

I have a situation where I need to create an application which supports multiple databases. Multiple databases means the client can use any of the database like Oracle, SQL Server, MySQL, PostgreSQL at first.

I was trying to use ORM like NHibernate or MyBatis. But they have their limitation and need expertise to use.

So I decide to user the Data Providers provided by Microsoft like ADO.NET, OLEDB, ODP.NET etc.

Is there any way so that the my logic of database keep same for all the database? I have tried IDbConeection, IDbCommand etc but they have a problem in case of Oracle (Ref Cursor).

I there any way to achieve this? Some link or guide would be appreciated.

Edit:
There is problem with the DBTypes because they are enum define differently with different data providers.

3 Answers

Answers 1

Well, real-life applications are complicated like that. Before you know it, you want to replace the UI with an App, expose your logic as a WCF service, change the e-mail service with another service provider, test pieces of your code while mocking the DAL and change the database with another one.

The usual way to deal with this is to pass all calls through an interface that separates the implementation from the caller. After that, you can implement the different DAL's.

Personally I usually go with this approach:

• First create a single DLL that contains all interfaces. Basically the idea is to expose all calls that your UI, App or whatever needs through the interface. From now on, your UI doesn't talk to databases or e-mail providers anymore.
• If you need to get access to the interface, you use a factory pattern. Never use 'new'; that will get you in trouble in the long run.
• It's not trivial to create this, and needs proper crafting. Usually I begin with a bare minimum version, hack everything else in the UI as a first version, then move everything that touches a DB or a service into the right project while creating interfaces and finally re-engineer everything until I'm 100% satisfied.
• Interfaces should be built to last. Sure, changes will happen over time, but you really want to minimize these. Think about what the future will hold, read up on what other people came up with and ensure your interfaces reflect that.

Basically you now have a working piece of software that works with a single database, mail provider, etc. So far so good.

Next, re-engineer the factory. Basically you want to use the configuration settings to pick the right provider (the right DLL that implements your interface) for your data. A simple switch can suffice in most cases.

At this point I usually make it a habit to make a ton of unit tests for the interfaces.

The last step is to create DLL's for the different database providers. One of these will be loaded at run-time in your application.

I prefer simple Linq to SQL (I also use the library from LinqConnect) because it's pretty fast. I simply start by copy-pasting the other database provider, and then re-engineer it until it works. Personally I don't believe in a magic 'support all sql databases' solution anymore: In my experience, some databases will handle certain queries a much, much faster than other databases - which means that you will probably end up with some custom code for each database anyways.

This is also the point where your unit tests are really going to pay off. Basically, you can just start with copy-paste and give it a test. If you're lucky, everything will run right away with decent performance... if not, you know where to start.

Build to last

Build things to last. Things will change:

• Think about updates and test them. Prefer automatic tests.
• You don't want to tinker with your Factory every day. Use Reflection, Expressions, Code generation or whatever your poison is to save yourself the trouble of changing code.
• Spend time writing tests. Make sure you cover the bulk. I cannot stress this enough; under pressure people usually 'save' time by not writing tests. You'll notice that this time that you 'save' will double back on you as support when you've gone live. Every month.

What about Entity Framework

I've seen a lot of my customers get into trouble with performance because of this. In the many times that I've tested it, I had the same experience. I noticed customers hacking around EF for a lot of queries to get a bit of decent performance.

To be fair, I gave up a few years ago, and I know they have made considerable performance improvements. Still, I would test it (especially with complex queries) before considering it.

If I would use EF, I'd implement all EF stuff in a 'database common DLL', and then derive classes from that. As I said, not all databases are the same with queries - and you might want to implement some hacks that are necessary to get decent performance. Your tests will tell.

Bonuses

Other reasons for programming through interfaces has a lot of advantages in combination with proxy's. To name a few, you can easily create log sinks, caching, statistics, WCF, etc. by simply implementing the same interface. And if you end up hating your current OR mapper some day, you can just throw it away without touching a single line of your app.

Answers 2

I believe Microsoft's Data Access Components would be suitable to you. https://en.wikipedia.org/wiki/Microsoft_Data_Access_Components

Answers 3

How about writing microservices and connect them by using a rest api? You (and maybe your team) could provide a core application which handles the logic and the ui. This is still based on your current technology. But instead of adding directly some kind of database connection, you could provide multiple types of microservices (based on asp.net or core) providing a rest api. You get your data from each database from such a microservice. So you would develop 1 micro service for e.g. MySQl and another one for MsSQL and when a new customer comes up with oracle you write a new small microservice which handles your expected API.

More info (based on .net core) is here: https://docs.asp.net/en/latest/tutorials/first-web-api.html

I think this is a teams discussion, which kind of technology you decide to use. But today I would recommend writing a micro service. It makes the attachment of a new app for a e.g. mobile device also much easier :)

Read More

Using Doctrine and Symfony to create Polymorphic like associations

I'm attempting to have an Fileable trait that will give provide an Entity with methods to CRUD Files based on the File Entity mentioned below.

After reading the documentation on Doctrine and searching the internet, the best I could find is Inheritance Mapping but these all require the subclass to extend the superclass which is not ideal as the current Entities already extend other classes. I could have FileFoo entity and a FileBar entity but this gets too messy and requires an extra join (super -> sub -> entity).

Alternatively, I could have a File Entity which has many columns for Entities (so foo_id for the Foo object, bar_id for the bar object and so on) but this gets messy and would require a new column for every entity that I'd want to add the Fileable trait too.

So to the questions: Am I thinking about how I want to hold data incorrectly? Is there some features/functions in Doctrine/Symfony that I've missed? Do you think I feature like this would be added if I were to fork Doctrine to add this feature, also where should I look?

<?php /**  * File  *  * @ORM\Table()  * @ORM\Entity()  * @ORM\HasLifecycleCallbacks()  */ class File {     /**      * @var integer      *      * @ORM\Column(type="integer")      * @ORM\Id()      * @ORM\GeneratedValue()      */     protected $id;     /**      * @var string      *      * @ORM\Column(type="string")      */     protected $entityName;     /**      * @var string      *      * @ORM\Column(type="string")      */     protected $entityId; ... 

2 Answers

Answers 1

I accomplished a similar thing using Inheritance defined in traits, which alongside interfaces, basically gave me what a multiple extend would give.

Answers 2

Take a look at embeddables or you could use traits.

Read More

Doctrine 2 Symfony 2 Getting foreign key entities without mapping

so I am fairly new to Symfony and Doctrine. I would like to know if there's a way to ask doctrine what foreign keys are in place, but without having to map relationships in the model.

For example, say you have CoreBundle:Company which is ALWAYS going to be present, and then you have OptionalBundle:Client which will extend Company with a @OneToOne mapping relationship, adding a few more fields in itself. The thing is, that since OptionalBundle may not be present, I don't want explicit mapping from CoreBundle to OptionalBundle.

Now say a user comes along and attempts to delete Company(5). If the entity was fully mapped it would delete both with cascading, but since the bundle is not going to be aware of a mapped relationship it would end up deleting the Company only - I want to produce an error rather than cascading the deletion.

If this is possible quite easily, then I would also want to take it another step further and say, what entities (class and id) have foreign keys that I can show the data to the user, like

@CoreBundle:Company(5) ->     has @OptionalBundle:Client(3) linked, and     has @AnotherOptionalBundle:Supplier(12) linked 

My first instinct is to do a custom INFORMATION_SCHEMA lookup for the foreign keys but that will only give me table names...

PS I REALLY prefer not to have to use any third party vendors as I like to try and keep the dependencies down, even if it means reinventing the wheel

3 Answers

Answers 1

Have you considered defining the relationship as owned by the OptionalBundle side?

Answers 2

The only idea I come across is to pre-create class-mapping during Compiler Pass with some fallback type when secondary bundle is absent.

In compiler pass, check whether container has a secondary bundle loaded and use DoctrineOrmMappingsPass::createXmlMappingDriver with adjusted path. If found - map with secondary bundle's entity, if not - map it to null (for example).

Answers 3

Question 1

You could set the Client as the owner of the 1-to-1 relationship. However, depending on your use-case it might not be ideal, but if that works for you it would really be the simplest solution, as pointed out by ABM_Dan.

Barring that, the best option for you is probably to use Doctrine event subscribers and to hook on the preDelete event, where you would remove the associated Client, before the Company itself is removed - if cascading the deletion is really what you want.

By default both deletion will be in the same Doctrine transaction, meaning that if something goes wrong when deleting the Company, the Client deletion will be cancelled.

If you really want to trigger an error instead of this "manual cascading" of sorts, it is also possible in the preDelete method of the Doctrine subscriber.

The subscriber class can reside in your optional bundle even though it will act on an event associated to Company.

Doctrine event subscribers are separate from the regular Symfony event system. Newcomers often are not aware of its existence, but it can achieve a lot of interesting things.

Question 2

Still in your event subscribers, it is possible to hook on the postLoad event. This would allow you to request the database and load related entities directly into Company. You can create an event subscriber for Company in each bundle that requires it.

Although this is possible I really wonder if there might not be a better way. Using decorators might be a better solution. I found a Doctrine cookbook article about it.

Read More