adding a related record adds a spurious related record

airdrummer

unread,

Mar 11, 2021, 8:46:31 AM3/11/21

to Xataface

after i installed your fix for mobile view instead of add record, i have been getting spurious related records added when adding a different related record:-\ it's probably related (sorry;-) to my abuse of views;-}

i have a view of properties, with a relationship to the lotOwners table:

[lotOwnership]
lotOwnership.lotNum = "$lotNum"

and a relationship to the transactions table:

[transactions]
__sql__ = "SELECT * FROM transactions WHERE AccountID = '$lotNum' and date >= '$dateAquired' order by transactions.Date"

adding a transaction also results in a record being added to the lotOwners table:-\

my daily backup shows:

INSERT INTO `transactions` VALUES (110,'2021-03-10 20:19:39',135.00,2,4242);

INSERT INTO `transactions` VALUES (23,'2021-03-10 20:34:35',138.07,2,4243);

INSERT INTO `dataface__mtimes` VALUES ('transactions',1615408475);

and

INSERT INTO `lotOwnership` VALUES (110,0,'0000-00-00');

INSERT INTO `lotOwnership` VALUES (23,0,'0000-00-00');

INSERT INTO `dataface__mtimes` VALUES ('lotOwnership',1615408475);

glad i enabled history;-) this isn't a showstopper, as the spurious records have null ownerID and dateAquired...both are specified as not null in the d/b, so i guess xataface casts nulls to zeroes...and thus don't interfere with my business logic, but still...

Steve Hannah

unread,

Mar 11, 2021, 11:54:08 AM3/11/21

to xata...@googlegroups.com

That's odd. I don't see anywhere in the transactions relationship definition where it references the lotOwnership table. What is the "source" table of the transactions relationship?

--
You received this message because you are subscribed to the Google Groups "Xataface" group.
To unsubscribe from this group and stop receiving emails from it, send an email to xataface+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/xataface/f827813c-7a95-49db-9425-3dd9a064f244n%40googlegroups.com.

--

Steve Hannah

Web Lite Solutions Corp.

airdrummer

unread,

Mar 11, 2021, 12:28:37 PM3/11/21

to Xataface

CREATE TABLE transactions (
    AccountID INT NOT NULL,
    Date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, # DATE NOT NULL,
    Amount DECIMAL(10,2) NOT NULL,
    transactionType INT NOT NULL,
    transID INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
    INDEX (AccountID),
    INDEX (transactionType),
    FOREIGN KEY (AccountID)
      REFERENCES lots(lotNum)
      ON UPDATE CASCADE ON DELETE RESTRICT,
    FOREIGN KEY (transactionType)
      REFERENCES transactionTypes(TransactionType)
      ON UPDATE CASCADE ON DELETE RESTRICT
);

i've probably over-constrained...

Steve Hannah

unread,

Mar 11, 2021, 12:45:05 PM3/11/21

to xata...@googlegroups.com

Could be one of those "ON UPDATE CASCADE" rules.

To view this discussion on the web visit https://groups.google.com/d/msgid/xataface/e8690772-e96f-4111-aab1-8bed5697c999n%40googlegroups.com.

airdrummer

unread,

Mar 11, 2021, 5:45:48 PM3/11/21

to Xataface

i'll try removing them...

airdrummer

unread,

Mar 11, 2021, 5:58:08 PM3/11/21

to Xataface

just did a dump w/meta, guess that on update stuff (from my original sql) has gotten stripped in the several migrations i've done:-}

CREATE TABLE `transactions` (
`AccountID` int(11) DEFAULT NULL,
`Date` timestamp NOT NULL DEFAULT current_timestamp(),
`Amount` decimal(10,2) NOT NULL,
`transactionType` int(11) DEFAULT NULL,
`transID` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `transactions` (`AccountID`, `Date`, `Amount`, `transactionType`, `transID`) VALUES
(74, '2013-06-01 04:00:00', -7.50, 3, 16),

...

ALTER TABLE `transactions`
ADD PRIMARY KEY (`transID`),
ADD KEY `AccountID` (`AccountID`),
ADD KEY `transactionType` (`transactionType`);
ALTER TABLE `transactions`
MODIFY `transID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4244;

so not that...

airdrummer

unread,

Mar 11, 2021, 6:08:59 PM3/11/21

to Xataface

CREATE VIEW `currentLotOwners` AS
select `owners`.`ownerID` AS `ownerID`,
        `owners`.`firstNAME` AS `firstNAME`,
        `owners`.`LASTNAME` AS `LASTNAME`,
        `owners`.`role` AS `role`,
        `lots`.`lotNum` AS `lotNum`,
        `lots`.`NUM` AS `num`,
        `streets`.`name` AS `name`,
        `lotOwnership`.`dateAquired` AS `dateAquired`,
        `owners`.`email` AS `email`,
        `owners`.`phone` AS `phone`,
        `owners`.`eDeliveryConsent` AS `eDeliveryConsent`
from (((`lots`
    join `streets`
        on(`lots`.`streetID` = `streets`.`ID`))
    join `owners`
         on(`owners`.`ownerID` = (
             select `lotOwnership`.`ownerID`
                 from `lotOwnership`
                     where `lotOwnership`.`lotNum` = `lots`.`lotNum`
                     order by `lotOwnership`.`dateAquired` desc limit 1)))
    join `lotOwnership`
         on(`owners`.`ownerID` = `lotOwnership`.`ownerID` and `lotOwnership`.`lotNum` = `lots`.`lotNum`))
    order by `streets`.`name`,`lots`.`NUM` ;

airdrummer

unread,

Mar 11, 2021, 8:30:18 PM3/11/21

to Xataface

i can't seemto replicate the problem with debug_sgl:-\ but i did see a problem with the transactions order by:

as i posted above the relationship is:

[transactions]
__sql__ = "SELECT * FROM transactions WHERE AccountID = '$lotNum' and date >= '$dateAquired' order by transactions.Date"

but the sql xataface generates is:

Performing query: 'SELECT COUNT(*) as num from `transactions` where `AccountID` = '141' and `date` >= '2013-12-20' order by `transactions`.`Date` asc'

Performing query: 'select `transactions`.`AccountID`, ifnull(convert_tz(`transactions`.`Date`,'SYSTEM','UTC'), `transactions`.`Date`) as `Date`, `transactions`.`transactionType`, `transactions`.`Amount`, `transactions`.`transID` from `transactions` where `AccountID` = '141' and `date` >= '2013-12-20' order by `transID` asc LIMIT 0,30'

airdrummer

unread,

Mar 12, 2021, 8:48:49 AM3/12/21

to Xataface

sorry wrong view...the problem exists in the duesMailing view:

[transactions]
__sql__ = "SELECT * FROM transactions

WHERE transactions.AccountID = '$lotNum'
AND transactions.Date >=
     (SELECT lotOwnership.dateAquired from lotOwnership
        WHERE lotOwnership.lotNum = '$lotNum'
             order by dateAquired DESC LIMIT 1)
order by transactions.Date ASC"

action:label = "current ledger"

and it does break my business logic when a related transaction is deleted...the lotownership record is also deleted:-( now _that's_ what i call a showstopper:-(

On Thursday, March 11, 2021 at 11:54:08 AM UTC-5 Steve Hannah wrote:

Steve Hannah

unread,

Mar 12, 2021, 8:54:22 AM3/12/21

to xata...@googlegroups.com

Is that a relationship definition? If so, Xataface just isn't that smart (to handle a nested subquery). I'm surprised it didn't just crash on the add record.

--
You received this message because you are subscribed to the Google Groups "Xataface" group.
To unsubscribe from this group and stop receiving emails from it, send an email to xataface+u...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/xataface/e936727b-1b3c-446e-811a-01968fcef349n%40googlegroups.com.

airdrummer

unread,

Mar 12, 2021, 8:59:46 AM3/12/21

to Xataface

also hindering debugging is the lack of sql for anything other than selects...no add/delete:

here is all the output after selecting a transaction to delete:

Are you sure you want to remove the following records from the relationship 'current ledger'?

2: payment

after deletion, the spurious lotownership record was also deleted

airdrummer

unread,

Mar 12, 2021, 9:00:49 AM3/12/21

to Xataface

ok, i'll try to redo my relationship...

airdrummer

unread,

Mar 12, 2021, 9:18:58 AM3/12/21

to Xataface

good news! i replaced lotOwnership in the relationship with currentLotOwners (a view which encapsulates the lotownership logic) and everything is shipshape now!-)

on deletion of a related transaction, the deletion goes thru, with this reassuring msg(-:

Errors occurred trying to remove records
Failed to delete record 'Ross 16 Barnswallow Ct' because of an SQL error: '' : 'DELETE FROM `currentLotOwners` WHERE `currentLotOwners`.`lotNum` = '141''

but i do wish debug+sql would output more than selects...

airdrummer

unread,

Mar 12, 2021, 9:44:06 AM3/12/21

to Xataface

1 last quibble: when i display the current ledger

(transactions for selected property:

https://cardinalglen.org/cghoaDF/index.php?-table=currentLotOwners&-action=related_records_list&-cursor=16&-skip=0&-limit=30&-mode=list&-sort=role+desc&-recordid=currentLotOwners%3FlotNum%3D141&-relationship=transactions)

the export current ledger function

(https://cardinalglen.org/cghoaDF/index.php?-table=currentLotOwners&-action=export_csv&-cursor=16&-skip=0&-limit=30&-mode=list&-sort=role+desc&-recordid=currentLotOwners%3FlotNum%3D141&-relationship=transactions&--related=1)

exports the wrong acct/lotnum)-:

AccountID Date transactionType Amount transID

95: 10 Coloma Ct 2013-04-25 2: payment 125 167

...

airdrummer

unread,

Mar 12, 2021, 9:51:36 AM3/12/21

to Xataface

all these issues are probably just artifacts of my convoluted views, not anything wrong with xataface...i hacked these together while teaching myself sql, before ever trying xataface...it's a testament to xataface that it works as well as it does given my fuzzing attack;-)

Reply all

Reply to author

Forward