How do I delete rows from one table using another table in WHERE clause?

U01G4K5E0J2
U01G4K5E0J2 Posts: 194 πŸ§‘πŸ»β€πŸš€ - Cadet
edited June 2023 in Propel ORM

this might be more a propel question, but how do I delete rows from one table using another table in WHERE clause? use{RelatedTableName} doesn't seem to work here

$this->getFactory()
    ->createSomeQuery()
    ->useRelatedEntity()
    ->filterByRelatedEntityColumn($someValue)
    ->endUse()
    ->delete(); // doesn't work for me
Tagged:

Comments

  • U01SE4SRCU9
    U01SE4SRCU9 Posts: 68 πŸ§‘πŸ»β€πŸš€ - Cadet

    A good example of why I have issues with Propel...

    The simplest solution would probably be to gather the data you need from the other table into a variable and thus use its data for your - separate - delete query.
    Or, alternatively, you can eschew Propel altogether and formulate your query in raw SQL.

  • U01G4K5E0J2
    U01G4K5E0J2 Posts: 194 πŸ§‘πŸ»β€πŸš€ - Cadet

    yeah, I thought about somthing like this but I am not sure how my script will behave if I select 100k ids from one table, put them in an array and use in another query in filterBySomeFk_In($aLotOfIds)

  • U01SE4SRCU9
    U01SE4SRCU9 Posts: 68 πŸ§‘πŸ»β€πŸš€ - Cadet
    edited July 2021

    @U01G4K5E0J2 Whoops... then forget about that option entirely. We've had our share of issues with queries with so many parameters in an IN... (it'll complain about too many parameters and keep erroring, won't work)
    In this case, use raw SQL. In my opinion at least, that is the best solution in such cases.

  • U01G4K5E0J2
    U01G4K5E0J2 Posts: 194 πŸ§‘πŸ»β€πŸš€ - Cadet

    damn, cannot say I am surprised, still bummed about that, thanks @U01SE4SRCU9

  • U01SE4SRCU9
    U01SE4SRCU9 Posts: 68 πŸ§‘πŸ»β€πŸš€ - Cadet

    You're welcome, @U01G4K5E0J2

  • U01SE4SRCU9
    U01SE4SRCU9 Posts: 68 πŸ§‘πŸ»β€πŸš€ - Cadet
    edited July 2021

    @U01G4K5E0J2 Also, a tip: take a look at \Spryker\Zed\ProductDiscontinuedProductLabelConnector\Persistence\ProductDiscontinuedProductLabelConnectorRepository::getProductAbstractIdsToBeLabeled . That's how you should not do it. Check how many parameters this query uses for its NOT_IN in your case. If you see it getting dangerously high (more tens of thousands) then overwrite and reformulate that too into raw SQL before the amount of parameters hits 65k (that's when you'll start getting the errors and suddenly your logs will become inflated to incredible sizes).