[doctrine-user] Is there a Doctrine pattern for a common JOIN? | Am I using Doctrine correctly?

[Dennis Fedco]

I am just starting to use Doctrine again after a long break.  I used 
Doctrine before lightly, more so following simple examples in a tutorial.

Now, I am evaluating using it in a real project that uses multiple 
databases and joins that span across several databases and tables.  So far 
I am using it for new functionality only.

Functionality is as follows -- given a list of many services, each service 
falls into one of the few categories.  To select required service faster, 
users are to select their category first, upon which they are shown 
services from that category only and then they can select appropriate 
service.  I've decided to create two entities in this case, ServiceLineItem 
(description), and ServiceCategory.  And set up ManyToOne relationship like 
so:

class ServiceLineItem
{
/**
 * @ManyToOne(targetEntity="ServiceCategory")
 */
private $category;
}

*Task*
Select all services in ServiceLineItem, with chosen ServiceCategory of 
"Testing".

Using SQL I would do it like so:
-- Exhibit 1 -- 
select * 
from service_line_item 
join service_category on service_line_item.service_category_id = 
service_category.id
where service_category.category = "Testing";

Or I could do it like so, without the JOIN if I have to:
$id = ... select id from service_category where category = "Testing";
--
select * from service_line_item where service_category_id = $id;

*My problem is as so:*

   - My expectations of Doctrine were that there would be a simple 
   auto-magical way to retrieve data for a common JOIN pattern like in Exhibit 
   1.  
   The relationship association is already defined!  Might there be an 
   extra method to invoke that connection implicitly?
   
   So far, people tell me that I have to write a custom DQL with custom 
   class extending EntityRepository and using createQueryBuilder(), where(), 
   setParameter() and innerJoin().
   It may be fine and great for extra custom cases, but it is a lot of 
   overhead to learn new API and use it in the code especially for something 
   fairly common and simple like my use case (I would think).
   I ponder the efforts of learning DQL API when I already know how to call 
   an already-complex enough and already-known-to-me SQL directly, with 
   mysqli.  I see this (DQL, etc) as being potentially more trouble than it's 
   worth.
   
The above has me question how far I want to go into Doctrine if it is going 
to place a lot of custom DQL API into the codebase both for me and for 
others to learn.

Alternatively, it seems it'd be easier to do something like this instead, 
effectively breaking removing the JOIN, avoiding custom DQL, but leaving no 
power for Doctrine 
Entity\ServiceLineITem

Am I using this correctly?   Is there a philosophy behind Doctrine that I 
am missing?  Did I perhaps miss a feature?

My trial move towards using Doctrine was in hopes that it will manage SQL 
creation for me (it mostly does), use InnoDB (it does), set up 
FK+Referential Integrity (it does), and simplify data Input/Output (so far 
... it does not, not to my "lazy programmer" satisfaction)

Thanks!

-- 
You received this message because you are subscribed to the Google Groups 
"doctrine-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to doctrine-user+unsubscr...@googlegroups.com.
To post to this group, send email to doctrine-user@googlegroups.com.
Visit this group at http://groups.google.com/group/doctrine-user.
For more options, visit https://groups.google.com/d/optout.

[doctrine-user] How to best define (or handle) a foreign keys in legacy tables that were not handled by Doctrine's "convert-mapping"/"generate-entities"?

[Dennis Fedco]

I am working with a legacy codebase, and a database that has no foreign key 
constraints defined, nor has referential integrity.
And what I'm doing is I am modeling my Doctrine entities in PHP to match 
MySQL tables, so that I can use Doctrine inside PHP code.

And so first I build my Entities to match the table and the data types in 
the table.  But frequently I come across a situation like so:  I have two 
Doctrine Entities:  Item and Data 
Actual schema & tables are:

item(id, data ... );
data(id, data, item_id);  //where item_id is a foreign key to item

I also run these to help me generate the Entities:
vendor/bin/doctrine orm:convert-mapping --namespace="Entity\\" --force  --
from-database annotation ./module/Operations/src/
vendor/bin/doctrine orm:generate-entities ./module/Operations/src/ --
generate-annotations=true
(^^ Doctrine reads MySQL tables and creates PHP code with Annotations and 
getters and setters for me, and mostly gets it right)

Now, looking at the generated Data Entity, I see this:

class Data
{
/**
 * @var integer 
 * @Column(name="item_id", type="integer", precision=0, scale=0, 
nullable=false, unique=false)
 */
private $itemId;
}

That is all good. But I note that the foreign key is *not recognized.  *There 
is no relationship defined.  My question is:

   - Do I do anything about this?  Do I leave it as is?
   - Do I fix it up somehow?  How?  Make it into $item and define OneToMany 
   or ManyToOne relationships?  Bidirectional, Onedirectional?

I know that Doctrine will handle things well either way I choose to go.  
But I am not clear on which way is best for me.  

I am looking for a best practice.  For example, am I better in the long run 
to always modify such foreign keys to define a Relationship of some type, 
or do I leave them as-is, until some need arises?


I suppose not all foreign key situations are the same, but in this case, I 
often run SQL queries like so:


SELECT * FROM data WHERE item_id = 3;
SELECT * FROM item JOIN data ON data.item_id = item.id;


Thanks!
Dennis

-- 
You received this message because you are subscribed to the Google Groups 
"doctrine-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to doctrine-user+unsubscr...@googlegroups.com.
To post to this group, send email to doctrine-user@googlegroups.com.
Visit this group at https://groups.google.com/group/doctrine-user.
For more options, visit https://groups.google.com/d/optout.

[doctrine-user] Best Practice for Retrieving records by using Foreign Key

[Dennis Fedco]

I found this: 
http://pietervogelaar.nl/doctrine-2-use-foreign-key-as-field-in-dql

The posts in the link are from 2-3 years ago and recommend using an 
IDENTITY function.  I am curious if using IDENTITY is still a recommended 
practice?

I find it that in some of my code I have been unwittingly using the "double 
approach" such as having both relation and the foreign key defined (as in 
the link), and using one which was more appropriate.

-- 
You received this message because you are subscribed to the Google Groups 
"doctrine-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to doctrine-user+unsubscr...@googlegroups.com.
To post to this group, send email to doctrine-user@googlegroups.com.
Visit this group at https://groups.google.com/group/doctrine-user.
For more options, visit https://groups.google.com/d/optout.

Re: [doctrine-user] Unexpected differences in Result Set data structure for similar DQL statements

[Dennis Fedco]

Thanks.

I have tried the new DQL and results are as follows:

   - I now have a one-dimensional array with 4 indices, each holding the 
   "Item" Entity
   - One "Product" entity is loaded as part of the above entity (one for 
   each index location), under "product" member variable.
   - The "Product" is no longer a proxy

It now looks more properly OO-loaded.  That is good.


But I do note that I come across the same issue in that the format changes 
- it is now a single-dimensional array rather than double.


That is... if I use "SELECT i, p", I get a one-dimensional array of Item 
Objects (which have Product objects loaded).

If I use "SELECT i as item, p as product", I get a one-dimensional array, 
where each index contains a one-dimensional array with key of "item", which 
points to Item object, and Item object has a property "product" that points 
to Product object.


The format changes are generally not a big deal for me thankfully, but I do 
need to go into the code and fix up any and all references to how things 
are called. This can be a pain. That is to say, *different queries are not 
interchangeable* when it comes to using results.


Here is retrieval of same "product.model" data (using suggested DQL but 
varying SELECT params):

//3 different ways to retrieve the same data by varying SELECT params
$ret[0]->getProduct()->getModel();  //SELECT i, p

$ret[0]['item']->getProduct()->getModel();  //SELECT i as item, p as product
$ret[0]['model'];   //SELECT i as item, p.model as 
model

Looking this I actually prefer the plain "SELECT i, p" better -- it's more 
OO, just an array of Objects with no arrays in the way other then the 
top-level one


I should also note that due to the association here, using "SELECT i, p as 
product" and "SELECT i, p" and "SELECT i, p as fnord_bottle" all give 
identical results.  That is, "as " is ignored for p.


*The "May be a bug":*

As far as the "bug", yep, still there when using original query.  Generated 
SQL looks something like this:

SELECT 
i0_.id AS id_0,
p1_.id AS id_32,
p1_.model AS model_33,
i0_.product_id AS product_id_52
FROM item i0_ 
INNER JOIN product p1_ ON (i0_.product_id = p1_.id)
WHERE ...;


And Result Set from SQL looks like so:

+--++---+--++---+
| id_0 || id_32 | model_33 || product_id_52 |
+--++---+--++---+
|  421 ||   159 |  AA  ||   159 |
|  422 ||   159 |  AA  ||   159 |
|  423 ||   158 |  BB  ||   158 |
|  424 ||   157 |  CC  ||   157 |
+--++---+--++---+
4 rows in set (0.00 sec)


Also, since original post, for the "bug" query, I have changed it to use 
"WITH", with same messy results as before "(all mixed results in a 
one-dimension array)"

INNER JOIN ' . Product::class . ' p
WITH i.product = p.id

For now I liked the suggested DQL the best, as so:

SELECT i,p
    FROM ' . Item::class . ' i
JOIN i.product p
WHERE ...


Dennis




On Monday, May 9, 2016 at 3:10:03 PM UTC-4, Marco Pivetta wrote:
>
> Hi Dennis,
>
> On 6 May 2016 at 21:11, Dennis Fedco <dmat...@fedco-usa.com > 
> wrote:
>
>> *Peculiarity #1*
>>
>> I have some code, like so:
>>
>>
>> $query = $this->getEntityManager()->createQuery('
>> SELECT i, p
>> FROM ' . Item::class . ' i
>> INNER JOIN ' . Product::class . ' p
>> WHERE
>> i.productId = p.id
>> ');
>> $result = $query->getResult();
>>
>> foreach ($result as $row)
>> print get_class($row) . ' - ' . $row->getId() . "\n"
>>
>>
> The "WHERE i.productId = p.id" seems to be incorrect here. Is there an 
> association between Product and Item? If so, then you can simply rewrite 
> the query as following:
>
> 'SELECT i, p
> FROM ' . Item::class . ' i
> JOIN i.product p'
>
> This already hydrates the Product instance into the Item objects being 
> retrieved.
>  
>
>> I get this output, which is a one-dimensional array (I've added array 
>> indices for clarity):
>>
>> [0] => Application\Entity\Item - 284
>> [1] => DoctrineProxies\__CG__\Application\Entity\Product - 59
>> [2] => Application\Entity\Item - 302
>> [3] => Application\Entity\Item - 288
>> [4] => DoctrineProxies\__CG__\Application\Entity\Product - 58
>> [5] => Application\Entity\Item - 292
>> [6] => DoctrineProxies\__CG__\Application\Entity\Prod

[doctrine-user] Why is DoctrineProxies object so huge?

[Dennis Fedco]

I posted a question about DoctrineProxies construct that lives inside 
Doctrine\ORM\Configuration and creates huge proxy objects.

http://stackoverflow.com/questions/37076711/why-does-doctrine-orm-configurations-doctrineproxies-object-contain-the-unive

I will be curious to see if someone can offer feedback, or explain why it 
gets so big.

And particularly if doing something like

$instance = $em->find(Item::class, $id);


means passing a HUGE object (300Mb or more when printed out) via the 
assignment operator. 
i.e.is the object cloned, or just passed by reference?

Dennis

-- 
You received this message because you are subscribed to the Google Groups 
"doctrine-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to doctrine-user+unsubscr...@googlegroups.com.
To post to this group, send email to doctrine-user@googlegroups.com.
Visit this group at https://groups.google.com/group/doctrine-user.
For more options, visit https://groups.google.com/d/optout.

[doctrine-user] Unexpected differences in Result Set data structure for similar DQL statements

[Dennis Fedco]

*Peculiarity #1*

I have some code, like so:


$query = $this->getEntityManager()->createQuery('
SELECT i, p
FROM ' . Item::class . ' i
INNER JOIN ' . Product::class . ' p
WHERE
i.productId = p.id
');
$result = $query->getResult();

foreach ($result as $row)
print get_class($row) . ' - ' . $row->getId() . "\n"

I get this output, which is a one-dimensional array (I've added array 
indices for clarity):

[0] => Application\Entity\Item - 284
[1] => DoctrineProxies\__CG__\Application\Entity\Product - 59
[2] => Application\Entity\Item - 302
[3] => Application\Entity\Item - 288
[4] => DoctrineProxies\__CG__\Application\Entity\Product - 58
[5] => Application\Entity\Item - 292
[6] => DoctrineProxies\__CG__\Application\Entity\Product - 57

I found this to be peculiar because I am requesting both "i", and "p" in my 
select statement but I get count of four of "Item" returned and count of 3 
of "Product".  
Namely, I would typically expect 4 and 4.  That is, I'd expect this line to 
be present:
[2.5] => DoctrineProxies\__CG__\Application\Entity\Product - 59 --- That 
line is *missing* in my output. 

(Index of 2.5 to be interpreted as a made up index to signify that the line 
should have been the one between indices 2 and 3)

I suspect it is because the index of the Product happens to be the same in 
the join, 59 in this case)
But then I have to craft my code to accommodate for this, because normally 
I'd expect the Item and Product to alternate.

Is this behavior "by design"? 

*Peculiarity #2*

If I change the above DQL to say "p.model" instead, keeping everything else 
the same.  Like so:
SELECT i, p.model

Instead of an array of N Objects or Proxies, I now get an array of arrays.  
See below (with indices for clarity):
[0][0] => Application\Entity\Item - 284
[0][1] => AA (model for id==59)
[1][0] => Application\Entity\Item - 302
[1][1] => AA (model for id==59)
[2][0] => Application\Entity\Item - 288
[2][1] => BB (model for id==58)
[3][0] => Application\Entity\Item - 292
[3][1] => CC (model for id==57)

The *inconsistency* between the structure of result sets gotten from 
similar DQL statements is confusing and unexpected to me.

Is this behavior by design?  

Am I using ORM correctly in this such cases?
My goal is to get the contents of Item, but also pull model from Product

Dennis

-- 
You received this message because you are subscribed to the Google Groups 
"doctrine-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to doctrine-user+unsubscr...@googlegroups.com.
To post to this group, send email to doctrine-user@googlegroups.com.
Visit this group at https://groups.google.com/group/doctrine-user.
For more options, visit https://groups.google.com/d/optout.

[doctrine-user] Why use Entities for foreign keys (What is the benefit)?

[Dennis Fedco]

I am using Doctrine "in reverse" in that I have some legacy code that I am 
refactoring, 
and I am fitting in Doctrine to be on top of already existing tables.

So for example I have a table like so:

item(id, description, motor_id, product_id, drive_id);

I also have the corresponding tables for each of the foreign keys (not 
shown).
I have defined most but not all entities for the tables.  For example, I 
have one for the Product, but not for Motor or Drive.

I have noted that Using Doctrine, I can (for product_id, as an example):

   1. just leave it as an integer, without setting up an Entity
   2. set up a ManyToOne connection for the Product foreign key and treat 
   that property as an Entity

*Caveats*

.. I have tried some of this and I have found that using Entity so far can 
be a bit of an inconvenience.


Example without Entity:  I have code like so:

$productId = 44; //already known in the code
$db->query("update item set description = '...', product_id = $productId 
where id = $id;");


Transforming the above to Doctrine is easy enough.  And without an Entity 
for $productId property, product_id becomes just another thing to set, like 
so:

$item->setProductId($productId);


But when using an Entity instead there's a bit more work to be done.  I use 
something like so

//since $productId is already known I don't want to read it from DB again, 
so I use getReference()
$product = $this->em->getReference(Product::class, $productId);
$item->setProduct($product);

*Question*

In the grand scheme of things, I presume Doctrine will advocate the 
approach of using Entities for foreign keys (sometimes at the expense of 
setting up extra Proxies like above, or even just using $this->em->find(), 
to populate the foreign key Entity)

But, when I already have legacy code that's using known foreign keys, is 
using Entities worth it?
aka the legacy code already has the plumbing for integer keys, and 
rewriting for Entities will take a bit of work.  I am seeking some 
justification for that work, other than "It's the Doctrine Way, make it so".

-- 
You received this message because you are subscribed to the Google Groups 
"doctrine-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to doctrine-user+unsubscr...@googlegroups.com.
To post to this group, send email to doctrine-user@googlegroups.com.
Visit this group at https://groups.google.com/group/doctrine-user.
For more options, visit https://groups.google.com/d/optout.

[doctrine-user] Doctrine ProxyGenerator issues a PHP warning when trying rename()

[Dennis Fedco]

I am getting hundreds of errors like this

PHP Warning:  
rename(/tmp/__CG__NamespaceEntityProduct.php.57b1fb60e11ee5.96791162,/tmp/__CG__NamespaceEntityProduct.php):
 
Operation not permitted in 
/../vendor/doctrine/common/lib/Doctrine/Common/Proxy/ProxyGenerator.php on 
line 306

Have anyone had this issue and was it resolved?

Is it safe to have rename fail in this case?
Looking online it seems like suggested solutions include using copy and 
unlink 

Thanks,
Dennis

-- 
You received this message because you are subscribed to the Google Groups 
"doctrine-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to doctrine-user+unsubscr...@googlegroups.com.
To post to this group, send email to doctrine-user@googlegroups.com.
Visit this group at https://groups.google.com/group/doctrine-user.
For more options, visit https://groups.google.com/d/optout.

[doctrine-user] Re: Can I relate an Entity to a Many-To-Many relation in Doctrine?

[Dennis Fedco]

In other words, considering this example:  
https://gist.github.com/Ocramius/3121916
(which creates table user_usergroup as relation between entities User and 
UserGroup)

Can I create a new entity that uses table user_usergroup in a mapping 
directly?

The problem I am trying to solve is (using example of User and UserGroup)
How can I track the admin activity and history of adding a User to a 
UserGroup?

That is say I want to keep historical records of (admin_username, user, 
usergroup, date_added, date_removed), where user, usergroup are pulled from 
the user_usergroup relation.

Dennis

On Saturday, September 3, 2016 at 8:21:53 PM UTC-4, Dennis Matveyev wrote:
>
> Question -- can I model a Many-To-One relation going from an Entity into a 
> Many-To-Many relation?
>
> That is, from the example below, if I have entities Product and Option in 
> Many-To-Many relation , is it possible to connect PriceHistory entity to 
> relate to the ProductHasOptions relation?
>
> I am debating whether to do it as in the diagram below (if possible), or 
> if instead it is best to create ProductHasOption as an Entity with its own 
> id, and do "conventional" Many-To-One relations to/from relevant tables.
>
>
> 
>
>

-- 
You received this message because you are subscribed to the Google Groups 
"doctrine-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to doctrine-user+unsubscr...@googlegroups.com.
To post to this group, send email to doctrine-user@googlegroups.com.
Visit this group at https://groups.google.com/group/doctrine-user.
For more options, visit https://groups.google.com/d/optout.

[doctrine-user] How to model this ER diagram in Doctrine

[Dennis Fedco]

To follow up on my last question.. and if you like a challenge,  here is 
something I've designed and now trying to implement in Doctrine:

http://stackoverflow.com/questions/39397170/how-do-i-model-my-er-diagram-when-doctrine-wont-let-me-associate-composite-fore

I have made category_pricename relation to be its own Entity.  And I've 
done the same with product_pricename.

I seemed to have been able to transfer the ER Diagram amicably into 
Doctrine, by
1) making tables that held M:M relations into Entities
2) assigning surrogate primary keys to all tables/Entities/Relations - 
instead of trying to map composite primary keys onto foreign keys (Doctrine 
did not like).

I was curious to see if there is a more official Doctrine stance on if the 
above is the right approach.

Dennis

-- 
You received this message because you are subscribed to the Google Groups 
"doctrine-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to doctrine-user+unsubscr...@googlegroups.com.
To post to this group, send email to doctrine-user@googlegroups.com.
Visit this group at https://groups.google.com/group/doctrine-user.
For more options, visit https://groups.google.com/d/optout.

[doctrine-user] Is it a good practice to keep/maintain Doctrine and non-Doctrine ways of setting foreign keys?

[Dennis Fedco]

When foreign key integer value is already known ... Is it a good practice 
to have both methods for:

   - setting foreign ID directly by using its integer value 
   - setting foreign ID by using the associated Entity

Example:
$item = new Item();


//setting foreign key by its integer value

$item->setProductId(4);


//setting foreign key via its Doctrine Entity:

$productEntity = $em->find(Product::class, 4);

$item->setProduct($productEntity);



Dennis

-- 
You received this message because you are subscribed to the Google Groups 
"doctrine-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to doctrine-user+unsubscr...@googlegroups.com.
To post to this group, send email to doctrine-user@googlegroups.com.
Visit this group at https://groups.google.com/group/doctrine-user.
For more options, visit https://groups.google.com/d/optout.

[doctrine-user] How do you explain conceptual connection between an Integer and an Object Instance?

[Dennis Fedco]

Using this example:  Basic Many-To-One Mapping 


I am having a hard time connecting these concepts:

   - concept of a database column that is foreign key -- an integer number 
   (address_id)
   - concept of an object - or an instance of an object (class Address, new 
   Address())
   
In the example above they are connected conceptually yet they are 
definitely *not *the same.


I can see that maybe if you begin with Doctrine in mind, that is not much 
of an issue and you just accept it as given and not question it perhaps.


But to give you a further example of why I am bothered by this is this: ... 
I started out using MySQL way way back.  Now I am slowly converting my code 
to use Doctrine on existing tables.  So ie, to use the above example, I 
would start off having a class that returns an *integer* foreign key, 
because that is what is already used extensively in my code for a lot of 
things.  i.e.:


class User
{

/** @Column(name="address_id") */

private $addressId;

/*
 * Most of my existing code is written to use the _id - an Integer
 * So my getters would return that integer
 */
function getAddressId()
{
return $this->addressId;
}
}

/*
 * Sample Usage
 */

$addressId = $user->getAddressId();

Later when I get around to using more of Doctrine's power of JOINs/etc, I 
would create Address Entity, then Doctrinify my getters and upgrade my 
association on the User side as well:


class User
{
/**
 * @ManyToOne(targetEntity="Address")
 * @JoinColumn(name="address_id", referencedColumnName="id")
 */
private $address;

/*
 * Most of my existing code is written to use the _id - an Integer
 */
function getAddress()
{
return $this->address;
}
}

/*
 * Sample New Usage for old code:
 */

$addressId = $user->getAddress()->getId();

So in this case across my schemas I have a mix of foreign keys that are 
integers and ones that are Doctrine Entity objects.

They are not the same, obviously,


so I was curious to see if there is a way to think about them as "same" in 
some way ...   or to not try to understand the connection and just accept 
it as a paradigm shift.

-- 
You received this message because you are subscribed to the Google Groups 
"doctrine-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to doctrine-user+unsubscr...@googlegroups.com.
To post to this group, send email to doctrine-user@googlegroups.com.
Visit this group at https://groups.google.com/group/doctrine-user.
For more options, visit https://groups.google.com/d/optout.