问:
Efficiently selecting rows where a row in a related table exists
I've got a recurring pattern in a system that I'm currently working on, where, for example, I need to select all the users that have orders under a list of possible companies. Or needing to select users if a record of this user being flagged exists.

My users table contains 430,825 records, so this shouldn't really be that difficult to deal with. Right now I'm close, I have a query that get's that .047s execution time that I look for, but if I add one more piece to this, it gets real slow.

Here's my current query, the fast one:

```<code>select`UserID`
from`users`
where(`CompanyID`in('3e55d1bb-d8b6-11e4-b38f-b8ca3a83b4c8')
    or`UserID`in(select*
        from(select`UserID`
            from`invoices`
            where`CompanyID`in('3e55d1bb-d8b6-11e4-b38f-b8ca3a83b4c8')
            and`__Active`=1)`a`)
    or`UserID`in(select*
        from(select`UserID`
            from`quoterequests`
            where`CompanyID`in('3e55d1bb-d8b6-11e4-b38f-b8ca3a83b4c8')
            and`__Active`=1)`a`))
and(`UserID`in(select*
        from(select`UserID`
            from`userassociations`
            where`_Email`='brian@yeet.com'
            and`__Active`=1)`a`))
and(`UserID`in(select*
        from(select`UserID`
            from`usercustomerflags`
            where`CustomerFlagID`in(10,27,17,1,2,3,4,5,6)
            and`__Active`=1)`a`)
    or not exists(select 1
        from`usercustomerflags`
        where`__Active`=1
        and`users`.`UserID`=`UserID`))
and`Deleted`=0
order by`DateTimeAdded`desc
limit 50;
</code>```

(The extra select*from(...) is because of this https://stackoverflow.com/a/1434712/728236)

In the middle there, I'm pulling users by further by email address, while checking other related tables for emails that might be related to this user. Like, the next piece searches users including their CC addresses when quotes are sent out to customers.

```<code>select`UserID`
from`users`
where(`CompanyID`in('3e55d1bb-d8b6-11e4-b38f-b8ca3a83b4c8')
    or`UserID`in(select*
        from(select`UserID`
            from`invoices`
            where`CompanyID`in('3e55d1bb-d8b6-11e4-b38f-b8ca3a83b4c8')
            and`__Active`=1)`a`)
    or`UserID`in(select*
        from(select`UserID`
            from`quoterequests`
            where`CompanyID`in('3e55d1bb-d8b6-11e4-b38f-b8ca3a83b4c8')
            and`__Active`=1)`a`))
and(`UserID`in(select*
        from(select`UserID`
            from`userassociations`
            where`_Email`='brian@yeet.com'
            and`__Active`=1)`a`)
    or`UserID`in(select*
        from(select`UserID`
            from`userquotesemails`
            where`Email`='brian@yeet.com'
            and`__Active`=1)`a`))
and(`UserID`in(select*
        from(select`UserID`
            from`usercustomerflags`
            where`CustomerFlagID`in(10,27,17,1,2,3,4,5,6)
            and`__Active`=1)`a`)
    or not exists(select 1
        from`usercustomerflags`
        where`__Active`=1
        and`users`.`UserID`=`UserID`))
and`Deleted`=0
order by`DateTimeAdded`desc
limit 50;
</code>```

I've added the alternate table to search for emails, but now the query takes 3.016s, which is way, way slower. It seems odd that, as I was building this query, that last part seemed to be the tipping point in performance here, what would be the cause of this?

The first and second explains, respectively

```<code>+----+--------------------+-------------------+--+----------------+---------------------------------------------------------------------------------------------+------------------------------+------+-----------------------+---+-------+---------------------------------+
|  1 | PRIMARY            | <subquery6>       |  | ALL            |                                                                                |                              |      |                       |   | 0.00  | Using temporary; Using filesort |
|  1 | PRIMARY            | users             |  | eq_ref         | PRIMARY,UserID_UNIQUE,fk_users_1_idx,users_Customers                                        | PRIMARY                      |  144 | <subquery6>.UserID    | 1 | 50.00 | Using where                     |
|  6 | MATERIALIZED       | userassociations  |  | ref            | userassociations_UserID,userassociations__Email                                             | userassociations__Email      | 1026 | const                 | 3 | 10.00 | Using where                     |
| 10 | DEPENDENT SUBQUERY | usercustomerflags |  | ref            | usercustomerflags_UserID_idx                                                                | usercustomerflags_UserID_idx |  144 | sterling.users.UserID | 1 | 10.00 | Using where                     |
|  8 | DEPENDENT SUBQUERY | usercustomerflags |  | index_subquery | usercustomerflags_CustomerFlagID_idx,usercustomerflags_UserID_idx                           | usercustomerflags_UserID_idx |  144 | func                  | 1 | 4.95  | Using where                     |
|  4 | DEPENDENT SUBQUERY | quoterequests     |  | index_subquery | quoterequests_CompanyID,quoterequests_UserID,quoterequests__Latest,quoterequests_UserQuotes | quoterequests__Latest        |  145 | func                  | 2 | 5.00  | Using where                     |
|  2 | DEPENDENT SUBQUERY | invoices          |  | index_subquery | Invoice_UserID_idx,Invoice_CompanyID_idx,invoices_SampleRequests,invoices_LateOrdersBubble  | Invoice_UserID_idx           |  145 | func                  | 1 | 3.33  | Using where                     |
+----+--------------------+-------------------+--+----------------+---------------------------------------------------------------------------------------------+------------------------------+------+-----------------------+---+-------+---------------------------------+

+----+--------------------+-------------------+--+-----+---------------------------------------------------------------------------------------------+--------------------------------+------+-----------------------+--------+--------+-------------+
|  1 | PRIMARY            | users             |  | ref | fk_users_1_idx,users_Customers                                                              | users_Customers                |    4 | const                 | 227515 | 100.00 | Using where |
| 12 | DEPENDENT SUBQUERY | usercustomerflags |  | ref | usercustomerflags_UserID_idx                                                                | usercustomerflags_UserID_idx   |  144 | sterling.users.UserID |      1 | 10.00  | Using where |
| 10 | SUBQUERY           | usercustomerflags |  | ALL | usercustomerflags_CustomerFlagID_idx,usercustomerflags_UserID_idx                           |                                |      |                       |   3509 | 4.94   | Using where |
|  8 | SUBQUERY           | userquotesemails  |  | ref | userquotesemails_Email__Active,userquotesemails_UserID                                      | userquotesemails_Email__Active | 1027 | const,const           |      1 | 100.00 |             |
|  6 | SUBQUERY           | userassociations  |  | ref | userassociations_UserID,userassociations__Email                                             | userassociations__Email        | 1026 | const                 |      3 | 10.00  | Using where |
|  4 | SUBQUERY           | quoterequests     |  | ref | quoterequests_CompanyID,quoterequests_UserID,quoterequests__Latest,quoterequests_UserQuotes | quoterequests_CompanyID        |  144 | const                 |  16702 | 10.00  | Using where |
|  2 | SUBQUERY           | invoices          |  | ref | Invoice_UserID_idx,Invoice_CompanyID_idx,invoices_SampleRequests,invoices_LateOrdersBubble  | Invoice_CompanyID_idx          |  144 | const                 |  17678 | 10.00  | Using where |
+----+--------------------+-------------------+--+-----+---------------------------------------------------------------------------------------------+--------------------------------+------+-----------------------+--------+--------+-------------+
</code>```

Also, I've tried using joins, e.g. joining the invoices table, etc. but then I get the issue of having duplicate user rows per each invoice or quoterequest the joins receive, and grouping/distinct & ordering the resulting data became immensely slow, in the minutes.

I've also tried just the "exists" version of the first query, as suggesting by the doc https://dev.mysql.com/doc/refman/5.7/en/subquery-optimization-with-exists.html like so

```<code>select`UserID`
from`users`
where(`CompanyID`in('3e55d1bb-d8b6-11e4-b38f-b8ca3a83b4c8')
    or exists(select 1
        from`invoices`
        where`CompanyID`in('3e55d1bb-d8b6-11e4-b38f-b8ca3a83b4c8')
        and`__Active`=1
        and`users`.`UserID`=`UserID`)
    or exists(select 1
        from`quoterequests`
        where`CompanyID`in('3e55d1bb-d8b6-11e4-b38f-b8ca3a83b4c8')
        and`__Active`=1
        and`users`.`UserID`=`UserID`))
and(exists(select 1
        from`userassociations`
        where`_Email`='brian@yeet.com'
        and`__Active`=1
        and`users`.`UserID`=`UserID`))
and(exists(select 1
        from`usercustomerflags`
        where`CustomerFlagID`in(10,27,17,1,2,3,4,5,6)
        and`__Active`=1
        and`users`.`UserID`=`UserID`)
    or not exists(select 1
        from`usercustomerflags`
        where`__Active`=1
        and`users`.`UserID`=`UserID`))
and`Deleted`=0
order by`DateTimeAdded`desc
limit 50;
</code>```

But that gets me up to 5.516s, so that definitely doesn't seem like the right direction to make.

What is the most efficient way to select data the way that I'm trying? Or do I need to restructure some of my tables to get the performance I'm looking for?

I've isolated the smallest sub-problem and bottle neck I think I have. Here's my lighter query

```<code>select`users`.`UserID`,`users`.`_Customer`
from`users`
left join`userassociations`on`userassociations`.`UserID`=`users`.`UserID`
and`userassociations`.`__Active`=1
where(`users`.`Email`='brian@stumpyinc.com'
    or`userassociations`.`_Email`='brian@stumpyinc.com')
and`users`.`Deleted`=0
order by`users`.`DateTimeAdded`desc
limit 50;
</code>```

And the explain

```<code>+---+--------+------------------+--+-----+--------------------------------------------------------+-------------------------+-----+-----------------------+--------+--------+-------------+
| 1 | SIMPLE | users            |  | ref | users_getemail_INDEX,unify_email_INDEX,users_Customers | users_Customers         |   4 | const                 | 221463 | 100.00 | Using where |
| 1 | SIMPLE | userassociations |  | ref | userassociations_UserID                                | userassociations_UserID | 144 | sterling.users.UserID |      1 | 100.00 | Using where |
+---+--------+------------------+--+-----+--------------------------------------------------------+-------------------------+-----+-----------------------+--------+--------+-------------+
</code>```

This query takes about 1.5 seconds to execute

```<code>CREATE TABLE `users` (
  `UserID` char(36) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
    ...
  `Email` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    ...
  `DateTimeAdded` datetime DEFAULT NULL,
    ...
  `Deleted` int(1) NOT NULL DEFAULT '0',
    ...
  `_LatestInvoiceDateTimeAdded` datetime DEFAULT NULL,
  `_InvoiceCount` int(11) NOT NULL DEFAULT '0',
  `_Customer` varchar(512) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    ...
  PRIMARY KEY (`UserID`),
  UNIQUE KEY `UserID_UNIQUE` (`UserID`),
    ...
  KEY `users_getemail_INDEX` (`Email`(191),`_InvoiceCount`,`_LatestInvoiceDateTimeAdded`,`DateTimeAdded`),
  KEY `unify_email_INDEX` (`Email`(191),`UserID`),
    ...
  KEY `users_Customers` (`Deleted`,`DateTimeAdded`),
    ...
  KEY `users_DateTimeAdded` (`DateTimeAdded`,`UserID`),
  FULLTEXT KEY `users_FULLTEXT__Customer` (`_Customer`),
    ...
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `userassociations` (
   `UserAssociationID` binary(16) NOT NULL,
   `UserID` char(36) COLLATE utf8mb4_unicode_ci NOT NULL,
   `AssociatedUserID` char(36) COLLATE utf8mb4_unicode_ci NOT NULL,
   `_Email` varchar(256) COLLATE utf8mb4_unicode_ci NOT NULL,
   `__UserID` char(36) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `__Active` tinyint(1) NOT NULL DEFAULT '1',
   `__Added` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
   `__Updated` timestamp(6) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(6),
   PRIMARY KEY (`UserAssociationID`),
   KEY `userassociations_UserID` (`UserID`),
   KEY `userassociations_AssociatedUserID` (`AssociatedUserID`),
   KEY `userassociations___UserID` (`__UserID`),
   KEY `userassociations__Email` (`_Email`),
   CONSTRAINT `userassociations_AssociatedUserID` FOREIGN KEY (`AssociatedUserID`) REFERENCES `users` (`UserID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
   CONSTRAINT `userassociations_UserID` FOREIGN KEY (`UserID`) REFERENCES `users` (`UserID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
   CONSTRAINT `userassociations___UserID` FOREIGN KEY (`__UserID`) REFERENCES `users` (`UserID`) ON DELETE NO ACTION ON UPDATE NO ACTION
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
</code>```

Hmm... So it seemed like it was working, but I've found a pair of tables that it doesn't seem to be as efficient with, which is my users and invoices tables.

I've have these indexes:

```<code>users:    INDEX(`CompanyID`, `Deleted`, `DateTimeAdded`)
invoices: INDEX(`UserID`, `__Active`)
invoices: INDEX(`CompanyID`)
users:    INDEX(`UserID`, `Deleted`)
</code>```

and the query

```<code>select`users`.`UserID`,`users`.`DateTimeAdded`
from`users`
join`invoices`on`invoices`.`UserID`=`users`.`UserID`
and`invoices`.`__Active`=1
where`invoices`.`CompanyID`='3e55c8b4-d8b6-11e4-b38f-b8ca3a83b4c8'
and`users`.`Deleted`=0
order by`DateTimeAdded`desc
limit 200;
</code>```

This query alone takes .3 seconds, which feels slow to me, like it isn't making the best use of the indexes, especially because users only has 430,997 rows and invoices only has 194,180, and this looks like it should be a pretty simple query.

EDIT: Actually it's much worse than that, if the CompanyID given only includes ~4 rows, then this query takes 3.5 seconds

```<code>+---+--------+----------+--+-----+------------------------------------------------+-----------------------+-----+--------------------------------+------+--------+----------------------------------------------+
| 1 | SIMPLE | invoices |  | ref | Invoice_CompanyID_idx,invoices_UserID___Active | Invoice_CompanyID_idx | 144 | const                          | 7750 | 10.00  | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | users    |  | ref | users_UserID_Deleted                           | users_UserID_Deleted  | 148 | sterling.invoices.UserID,const |    1 | 100.00 |                                              |
+---+--------+----------+--+-----+------------------------------------------------+-----------------------+-----+--------------------------------+------+--------+----------------------------------------------+
</code>```

答:
Pretty sure you can replace all those subqueries in the WHERE clause with a combination of INNER and LEFT JOINs. Try this:

```<code>SELECT u.UserID
FROM users u
INNER JOIN userassociations ua ON ua.userid = u.userid
LEFT JOIN usercustomerflags uc ON uc.userid = u.userid AND uc.__Active =1
LEFT JOIN invoices i ON i.userid = u.userid AND i.__Active =1
LEFT JOIN quoterequests q ON q.userid = u.userid AND q.__Active =1
WHERE ua._Email ='brian@yeet.com'
AND ua.__Active =1
AND (uc.userid IS NULL
        OR (uc.userid IS NOT NULL AND uc.CustomerFlagID IN (10,27,17,1,2,3,4,5,6))
    )
AND (u.CompanyID = '3e55d1bb-d8b6-11e4-b38f-b8ca3a83b4c8'
        OR (i.CompanyID = '3e55d1bb-d8b6-11e4-b38f-b8ca3a83b4c8' AND i.userid IS NOT null)
        OR (q.CompanyID = '3e55d1bb-d8b6-11e4-b38f-b8ca3a83b4c8' AND q.userid IS NOT null)
    )
AND u.Deleted =0
ORDER BY u.DateTimeAdded desc
LIMIT 50;
</code>```

Body:
I've got a recurring pattern in a system that I'm currently working on, where, for example, I need to select all the users that have orders under a list of possible companies. Or needing to select users if a record of this user being flagged exists.

My users table contains 430,825 records, so this shouldn't really be that difficult to deal with. Right now I'm close, I have a query that get's that .047s execution time that I look for, but if I add one more piece to this, it gets real slow.

Here's my current query, the fast one:

select`UserID`
from`users`
where(`CompanyID`in('3e55d1bb-d8b6-11e4-b38f-b8ca3a83b4c8')
    or`UserID`in(select*
        from(select`UserID`
            from`invoices`
            where`CompanyID`in('3e55d1bb-d8b6-11e4-b38f-b8ca3a83b4c8')
            and`__Active`=1)`a`)
    or`UserID`in(select*
        from(select`UserID`
            from`quoterequests`
            where`CompanyID`in('3e55d1bb-d8b6-11e4-b38f-b8ca3a83b4c8')
            and`__Active`=1)`a`))
and(`UserID`in(select*
        from(select`UserID`
            from`userassociations`
            where`_Email`='brian@yeet.com'
            and`__Active`=1)`a`))
and(`UserID`in(select*
        from(select`UserID`
            from`usercustomerflags`
            where`CustomerFlagID`in(10,27,17,1,2,3,4,5,6)
            and`__Active`=1)`a`)
    or not exists(select 1
        from`usercustomerflags`
        where`__Active`=1
        and`users`.`UserID`=`UserID`))
and`Deleted`=0
order by`DateTimeAdded`desc
limit 50;

(The extra select*from(...) is because of this https://stackoverflow.com/a/1434712/728236)

In the middle there, I'm pulling users by further by email address, while checking other related tables for emails that might be related to this user. Like, the next piece searches users including their CC addresses when quotes are sent out to customers.

select`UserID`
from`users`
where(`CompanyID`in('3e55d1bb-d8b6-11e4-b38f-b8ca3a83b4c8')
    or`UserID`in(select*
        from(select`UserID`
            from`invoices`
            where`CompanyID`in('3e55d1bb-d8b6-11e4-b38f-b8ca3a83b4c8')
            and`__Active`=1)`a`)
    or`UserID`in(select*
        from(select`UserID`
            from`quoterequests`
            where`CompanyID`in('3e55d1bb-d8b6-11e4-b38f-b8ca3a83b4c8')
            and`__Active`=1)`a`))
and(`UserID`in(select*
        from(select`UserID`
            from`userassociations`
            where`_Email`='brian@yeet.com'
            and`__Active`=1)`a`)
    or`UserID`in(select*
        from(select`UserID`
            from`userquotesemails`
            where`Email`='brian@yeet.com'
            and`__Active`=1)`a`))
and(`UserID`in(select*
        from(select`UserID`
            from`usercustomerflags`
            where`CustomerFlagID`in(10,27,17,1,2,3,4,5,6)
            and`__Active`=1)`a`)
    or not exists(select 1
        from`usercustomerflags`
        where`__Active`=1
        and`users`.`UserID`=`UserID`))
and`Deleted`=0
order by`DateTimeAdded`desc
limit 50;

I've added the alternate table to search for emails, but now the query takes 3.016s, which is way, way slower. It seems odd that, as I was building this query, that last part seemed to be the tipping point in performance here, what would be the cause of this?

The first and second explains, respectively

+----+--------------------+-------------------+--+----------------+---------------------------------------------------------------------------------------------+------------------------------+------+-----------------------+---+-------+---------------------------------+
|  1 | PRIMARY            | <subquery6>       |  | ALL            |                                                                                |                              |      |                       |   | 0.00  | Using temporary; Using filesort |
|  1 | PRIMARY            | users             |  | eq_ref         | PRIMARY,UserID_UNIQUE,fk_users_1_idx,users_Customers                                        | PRIMARY                      |  144 | <subquery6>.UserID    | 1 | 50.00 | Using where                     |
|  6 | MATERIALIZED       | userassociations  |  | ref            | userassociations_UserID,userassociations__Email                                             | userassociations__Email      | 1026 | const                 | 3 | 10.00 | Using where                     |
| 10 | DEPENDENT SUBQUERY | usercustomerflags |  | ref            | usercustomerflags_UserID_idx                                                                | usercustomerflags_UserID_idx |  144 | sterling.users.UserID | 1 | 10.00 | Using where                     |
|  8 | DEPENDENT SUBQUERY | usercustomerflags |  | index_subquery | usercustomerflags_CustomerFlagID_idx,usercustomerflags_UserID_idx                           | usercustomerflags_UserID_idx |  144 | func                  | 1 | 4.95  | Using where                     |
|  4 | DEPENDENT SUBQUERY | quoterequests     |  | index_subquery | quoterequests_CompanyID,quoterequests_UserID,quoterequests__Latest,quoterequests_UserQuotes | quoterequests__Latest        |  145 | func                  | 2 | 5.00  | Using where                     |
|  2 | DEPENDENT SUBQUERY | invoices          |  | index_subquery | Invoice_UserID_idx,Invoice_CompanyID_idx,invoices_SampleRequests,invoices_LateOrdersBubble  | Invoice_UserID_idx           |  145 | func                  | 1 | 3.33  | Using where                     |
+----+--------------------+-------------------+--+----------------+---------------------------------------------------------------------------------------------+------------------------------+------+-----------------------+---+-------+---------------------------------+

+----+--------------------+-------------------+--+-----+---------------------------------------------------------------------------------------------+--------------------------------+------+-----------------------+--------+--------+-------------+
|  1 | PRIMARY            | users             |  | ref | fk_users_1_idx,users_Customers                                                              | users_Customers                |    4 | const                 | 227515 | 100.00 | Using where |
| 12 | DEPENDENT SUBQUERY | usercustomerflags |  | ref | usercustomerflags_UserID_idx                                                                | usercustomerflags_UserID_idx   |  144 | sterling.users.UserID |      1 | 10.00  | Using where |
| 10 | SUBQUERY           | usercustomerflags |  | ALL | usercustomerflags_CustomerFlagID_idx,usercustomerflags_UserID_idx                           |                                |      |                       |   3509 | 4.94   | Using where |
|  8 | SUBQUERY           | userquotesemails  |  | ref | userquotesemails_Email__Active,userquotesemails_UserID                                      | userquotesemails_Email__Active | 1027 | const,const           |      1 | 100.00 |             |
|  6 | SUBQUERY           | userassociations  |  | ref | userassociations_UserID,userassociations__Email                                             | userassociations__Email        | 1026 | const                 |      3 | 10.00  | Using where |
|  4 | SUBQUERY           | quoterequests     |  | ref | quoterequests_CompanyID,quoterequests_UserID,quoterequests__Latest,quoterequests_UserQuotes | quoterequests_CompanyID        |  144 | const                 |  16702 | 10.00  | Using where |
|  2 | SUBQUERY           | invoices          |  | ref | Invoice_UserID_idx,Invoice_CompanyID_idx,invoices_SampleRequests,invoices_LateOrdersBubble  | Invoice_CompanyID_idx          |  144 | const                 |  17678 | 10.00  | Using where |
+----+--------------------+-------------------+--+-----+---------------------------------------------------------------------------------------------+--------------------------------+------+-----------------------+--------+--------+-------------+

Also, I've tried using joins, e.g. joining the invoices table, etc. but then I get the issue of having duplicate user rows per each invoice or quoterequest the joins receive, and grouping/distinct & ordering the resulting data became immensely slow, in the minutes.

I've also tried just the "exists" version of the first query, as suggesting by the doc https://dev.mysql.com/doc/refman/5.7/en/subquery-optimization-with-exists.html like so

select`UserID`
from`users`
where(`CompanyID`in('3e55d1bb-d8b6-11e4-b38f-b8ca3a83b4c8')
    or exists(select 1
        from`invoices`
        where`CompanyID`in('3e55d1bb-d8b6-11e4-b38f-b8ca3a83b4c8')
        and`__Active`=1
        and`users`.`UserID`=`UserID`)
    or exists(select 1
        from`quoterequests`
        where`CompanyID`in('3e55d1bb-d8b6-11e4-b38f-b8ca3a83b4c8')
        and`__Active`=1
        and`users`.`UserID`=`UserID`))
and(exists(select 1
        from`userassociations`
        where`_Email`='brian@yeet.com'
        and`__Active`=1
        and`users`.`UserID`=`UserID`))
and(exists(select 1
        from`usercustomerflags`
        where`CustomerFlagID`in(10,27,17,1,2,3,4,5,6)
        and`__Active`=1
        and`users`.`UserID`=`UserID`)
    or not exists(select 1
        from`usercustomerflags`
        where`__Active`=1
        and`users`.`UserID`=`UserID`))
and`Deleted`=0
order by`DateTimeAdded`desc
limit 50;

But that gets me up to 5.516s, so that definitely doesn't seem like the right direction to make.

What is the most efficient way to select data the way that I'm trying? Or do I need to restructure some of my tables to get the performance I'm looking for?

I've isolated the smallest sub-problem and bottle neck I think I have. Here's my lighter query

select`users`.`UserID`,`users`.`_Customer`
from`users`
left join`userassociations`on`userassociations`.`UserID`=`users`.`UserID`
and`userassociations`.`__Active`=1
where(`users`.`Email`='brian@stumpyinc.com'
    or`userassociations`.`_Email`='brian@stumpyinc.com')
and`users`.`Deleted`=0
order by`users`.`DateTimeAdded`desc
limit 50;

And the explain

+---+--------+------------------+--+-----+--------------------------------------------------------+-------------------------+-----+-----------------------+--------+--------+-------------+
| 1 | SIMPLE | users            |  | ref | users_getemail_INDEX,unify_email_INDEX,users_Customers | users_Customers         |   4 | const                 | 221463 | 100.00 | Using where |
| 1 | SIMPLE | userassociations |  | ref | userassociations_UserID                                | userassociations_UserID | 144 | sterling.users.UserID |      1 | 100.00 | Using where |
+---+--------+------------------+--+-----+--------------------------------------------------------+-------------------------+-----+-----------------------+--------+--------+-------------+

This query takes about 1.5 seconds to execute

CREATE TABLE `users` (
  `UserID` char(36) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
    ...
  `Email` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    ...
  `DateTimeAdded` datetime DEFAULT NULL,
    ...
  `Deleted` int(1) NOT NULL DEFAULT '0',
    ...
  `_LatestInvoiceDateTimeAdded` datetime DEFAULT NULL,
  `_InvoiceCount` int(11) NOT NULL DEFAULT '0',
  `_Customer` varchar(512) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    ...
  PRIMARY KEY (`UserID`),
  UNIQUE KEY `UserID_UNIQUE` (`UserID`),
    ...
  KEY `users_getemail_INDEX` (`Email`(191),`_InvoiceCount`,`_LatestInvoiceDateTimeAdded`,`DateTimeAdded`),
  KEY `unify_email_INDEX` (`Email`(191),`UserID`),
    ...
  KEY `users_Customers` (`Deleted`,`DateTimeAdded`),
    ...
  KEY `users_DateTimeAdded` (`DateTimeAdded`,`UserID`),
  FULLTEXT KEY `users_FULLTEXT__Customer` (`_Customer`),
    ...
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `userassociations` (
   `UserAssociationID` binary(16) NOT NULL,
   `UserID` char(36) COLLATE utf8mb4_unicode_ci NOT NULL,
   `AssociatedUserID` char(36) COLLATE utf8mb4_unicode_ci NOT NULL,
   `_Email` varchar(256) COLLATE utf8mb4_unicode_ci NOT NULL,
   `__UserID` char(36) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `__Active` tinyint(1) NOT NULL DEFAULT '1',
   `__Added` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
   `__Updated` timestamp(6) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(6),
   PRIMARY KEY (`UserAssociationID`),
   KEY `userassociations_UserID` (`UserID`),
   KEY `userassociations_AssociatedUserID` (`AssociatedUserID`),
   KEY `userassociations___UserID` (`__UserID`),
   KEY `userassociations__Email` (`_Email`),
   CONSTRAINT `userassociations_AssociatedUserID` FOREIGN KEY (`AssociatedUserID`) REFERENCES `users` (`UserID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
   CONSTRAINT `userassociations_UserID` FOREIGN KEY (`UserID`) REFERENCES `users` (`UserID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
   CONSTRAINT `userassociations___UserID` FOREIGN KEY (`__UserID`) REFERENCES `users` (`UserID`) ON DELETE NO ACTION ON UPDATE NO ACTION
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

Hmm... So it seemed like it was working, but I've found a pair of tables that it doesn't seem to be as efficient with, which is my users and invoices tables.

I've have these indexes:

users:    INDEX(`CompanyID`, `Deleted`, `DateTimeAdded`)
invoices: INDEX(`UserID`, `__Active`)
invoices: INDEX(`CompanyID`)
users:    INDEX(`UserID`, `Deleted`)

and the query

select`users`.`UserID`,`users`.`DateTimeAdded`
from`users`
join`invoices`on`invoices`.`UserID`=`users`.`UserID`
and`invoices`.`__Active`=1
where`invoices`.`CompanyID`='3e55c8b4-d8b6-11e4-b38f-b8ca3a83b4c8'
and`users`.`Deleted`=0
order by`DateTimeAdded`desc
limit 200;

This query alone takes .3 seconds, which feels slow to me, like it isn't making the best use of the indexes, especially because users only has 430,997 rows and invoices only has 194,180, and this looks like it should be a pretty simple query.

EDIT: Actually it's much worse than that, if the CompanyID given only includes ~4 rows, then this query takes 3.5 seconds

+---+--------+----------+--+-----+------------------------------------------------+-----------------------+-----+--------------------------------+------+--------+----------------------------------------------+
| 1 | SIMPLE | invoices |  | ref | Invoice_CompanyID_idx,invoices_UserID___Active | Invoice_CompanyID_idx | 144 | const                          | 7750 | 10.00  | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | users    |  | ref | users_UserID_Deleted                           | users_UserID_Deleted  | 148 | sterling.invoices.UserID,const |    1 | 100.00 |                                              |
+---+--------+----------+--+-----+------------------------------------------------+-----------------------+-----+--------------------------------+------+--------+----------------------------------------------+

Body:
Pretty sure you can replace all those subqueries in the WHERE clause with a combination of INNER and LEFT JOINs. Try this:

SELECT u.UserID
FROM users u
INNER JOIN userassociations ua ON ua.userid = u.userid
LEFT JOIN usercustomerflags uc ON uc.userid = u.userid AND uc.__Active =1
LEFT JOIN invoices i ON i.userid = u.userid AND i.__Active =1
LEFT JOIN quoterequests q ON q.userid = u.userid AND q.__Active =1
WHERE ua._Email ='brian@yeet.com'
AND ua.__Active =1
AND (uc.userid IS NULL
        OR (uc.userid IS NOT NULL AND uc.CustomerFlagID IN (10,27,17,1,2,3,4,5,6))
    )
AND (u.CompanyID = '3e55d1bb-d8b6-11e4-b38f-b8ca3a83b4c8'
        OR (i.CompanyID = '3e55d1bb-d8b6-11e4-b38f-b8ca3a83b4c8' AND i.userid IS NOT null)
        OR (q.CompanyID = '3e55d1bb-d8b6-11e4-b38f-b8ca3a83b4c8' AND q.userid IS NOT null)
    )
AND u.Deleted =0
ORDER BY u.DateTimeAdded desc
LIMIT 50;


来源: stackexchange

问:
How to output data from iSQL to csv file _with_ headings?
I'm trying to query a Sybase ASA 8 database with the iSQL client and export the query results to a text file in CSV format. However the column headings are not exported to the file. There is no special option to specify that, neither in the iSQL settings nor in the OUTPUT statement.

The query and output statement looks like this:

```<code>SELECT * FROM SomeTable;
OUTPUT TO 'C:\temp\sometable.csv' FORMAT ASCII DELIMITED BY ';' QUOTE ''
</code>```

The result is a file like

```<code>1;Miller;Steve;1980-06-28
2;Jones;Martha;1965-11-02
3;Waters;Richard;1979-10-15
</code>```

while I'd like to have

```<code>ID;LASTNAME;FIRSTNAME;DOB
1;Miller;Steve;1980-06-28
2;Jones;Martha;1965-11-02
3;Waters;Richard;1979-10-15
</code>```

Any hints?

答:
I am able to use the isql command to output quoted CSV.

Example

```<code>$ isql $DATABASE $USERNAME $PASSWORD -b -d, -q -c
select username, fullname from users
</code>```

gives the result:

```<code>username,fullname
"jdoe","Jane Doe"
"msmith","Mark Smith"
</code>```

Command-line flags

(copied from the man page)

-b: Run isql in non-interactive batch mode. In this mode, the isql processes its standard input, expecting one SQL command per line.

-dDELIMITER: Delimits columns with delimiter.

-c: Output the names of the columns on the first row. Has any effect only with the -d or -x options.

-q: Wrap the character fields in double quotes.

Escaping Issue

You might run into problems if the query results contain double-quotes, though. The quotes aren't escaped properly, so they result in invalid CSV:

```<code>> select 'string","with"quotes' as quoted_string
quoted_string
"string","with"quotes"
</code>```

ContentLicense: CC BY-SA 3.0

Body:
I'm trying to query a Sybase ASA 8 database with the iSQL client and export the query results to a text file in CSV format. However the column headings are not exported to the file. There is no special option to specify that, neither in the iSQL settings nor in the OUTPUT statement.

The query and output statement looks like this:

SELECT * FROM SomeTable;
OUTPUT TO 'C:\temp\sometable.csv' FORMAT ASCII DELIMITED BY ';' QUOTE ''

The result is a file like

1;Miller;Steve;1980-06-28
2;Jones;Martha;1965-11-02
3;Waters;Richard;1979-10-15

while I'd like to have

ID;LASTNAME;FIRSTNAME;DOB
1;Miller;Steve;1980-06-28
2;Jones;Martha;1965-11-02
3;Waters;Richard;1979-10-15

Any hints?

Body:
I am able to use the isql command to output quoted CSV.

Example

$ isql $DATABASE $USERNAME $PASSWORD -b -d, -q -c
select username, fullname from users

gives the result:

username,fullname
"jdoe","Jane Doe"
"msmith","Mark Smith"

Command-line flags

(copied from the man page)

-b: Run isql in non-interactive batch mode. In this mode, the isql processes its standard input, expecting one SQL command per line.

-dDELIMITER: Delimits columns with delimiter.

-c: Output the names of the columns on the first row. Has any effect only with the -d or -x options.

-q: Wrap the character fields in double quotes.

Escaping Issue

You might run into problems if the query results contain double-quotes, though. The quotes aren't escaped properly, so they result in invalid CSV:

> select 'string","with"quotes' as quoted_string
quoted_string
"string","with"quotes"


问:
How to solve this `ImportError: Cannot import name url` in Django 2.0?
Okay I am following this https://tutorial.djangogirls.org/en/django_urls/ tutorial.

And when I run the code python manage.py runserver I get the following error:

```<code>Performing system checks...

Unhandled exception in thread started by <function check_errors.<locals>.wrapper at 0x7faf45be1d08>
Traceback (most recent call last):
  File "/home/piyush/.virtualenvs/myenv/lib/python3.5/site-packages/django/utils/autoreload.py", line 225, in wrapper
    fn(*args, **kwargs)
  File "/home/piyush/.virtualenvs/myenv/lib/python3.5/site-packages/django/core/management/commands/runserver.py", line 120, in inner_run
    self.check(display_num_errors=True)
  File "/home/piyush/.virtualenvs/myenv/lib/python3.5/site-packages/django/core/management/base.py", line 364, in check
    include_deployment_checks=include_deployment_checks,
  File "/home/piyush/.virtualenvs/myenv/lib/python3.5/site-packages/django/core/management/base.py", line 351, in _run_checks
    return checks.run_checks(**kwargs)
  File "/home/piyush/.virtualenvs/myenv/lib/python3.5/site-packages/django/core/checks/registry.py", line 73, in run_checks
    new_errors = check(app_configs=app_configs)
  File "/home/piyush/.virtualenvs/myenv/lib/python3.5/site-packages/django/core/checks/urls.py", line 13, in check_url_config
    return check_resolver(resolver)
  File "/home/piyush/.virtualenvs/myenv/lib/python3.5/site-packages/django/core/checks/urls.py", line 23, in check_resolver
    return check_method()
  File "/home/piyush/.virtualenvs/myenv/lib/python3.5/site-packages/django/urls/resolvers.py", line 397, in check
    for pattern in self.url_patterns:
  File "/home/piyush/.virtualenvs/myenv/lib/python3.5/site-packages/django/utils/functional.py", line 36, in __get__
    res = instance.__dict__[self.name] = self.func(instance)
  File "/home/piyush/.virtualenvs/myenv/lib/python3.5/site-packages/django/urls/resolvers.py", line 536, in url_patterns
    patterns = getattr(self.urlconf_module, "urlpatterns", self.urlconf_module)
  File "/home/piyush/.virtualenvs/myenv/lib/python3.5/site-packages/django/utils/functional.py", line 36, in __get__
    res = instance.__dict__[self.name] = self.func(instance)
  File "/home/piyush/.virtualenvs/myenv/lib/python3.5/site-packages/django/urls/resolvers.py", line 529, in urlconf_module
    return import_module(self.urlconf_name)
  File "/usr/lib/python3.5/importlib/__init__.py", line 126, in import_module
    return _bootstrap._gcd_import(name[level:], package, level)
  File "<frozen importlib._bootstrap>", line 986, in _gcd_import
  File "<frozen importlib._bootstrap>", line 969, in _find_and_load
  File "<frozen importlib._bootstrap>", line 958, in _find_and_load_unlocked
  File "<frozen importlib._bootstrap>", line 673, in _load_unlocked
  File "<frozen importlib._bootstrap_external>", line 665, in exec_module
  File "<frozen importlib._bootstrap>", line 222, in _call_with_frames_removed
  File "/home/piyush/Desktop/Djangosite/mysite/urls.py", line 16, in <module>
    from django.urls import include, url
ImportError: cannot import name 'url'
</code>```

And mysite/urls.py :

```<code>from django.urls import include, url
from django.contrib import admin

urlpatterns = [
    url(r'^admin/', admin.site.urls),
    url(r'', include('blog.urls')),
]
</code>```

And blog/urls.py :

```<code>from django.conf.urls import url
from . import views
urlpatterns = [
    url(r'^$', views.post_list, name='post_list'),
]
</code>```

Sorry for the ginormous code, and the version of Django I am using here is 2.0.

But then I tried using the version used in tutorial that is 1.11.8, I got the same error but the last line of the error says ImportError: cannot import name 'include' instead of ImportError: cannot import name 'url'

I don't understand how to use the official documentation yet, I started Django yesterday itself. Sorry for the inconvenience.

Danke.

答:
blog/urls.py:

```<code>from django.urls import path
from . import views
urlpatterns = [
  path(r'^$', views.post_list, name='post_list'),
]
</code>```

mysite/urls.py:

```<code>from django.urls import include, path
from django.contrib import admin
urlpatterns = [
  path('admin/', admin.site.urls),
  path(r'', include('blog.urls')),
]
</code>```

As suggested follow the documentation:

https://docs.djangoproject.com/en/2.0/intro/tutorial01/

Body:
Okay I am following this https://tutorial.djangogirls.org/en/django_urls/ tutorial.

And when I run the code python manage.py runserver I get the following error:

Performing system checks...

Unhandled exception in thread started by <function check_errors.<locals>.wrapper at 0x7faf45be1d08>
Traceback (most recent call last):
  File "/home/piyush/.virtualenvs/myenv/lib/python3.5/site-packages/django/utils/autoreload.py", line 225, in wrapper
    fn(*args, **kwargs)
  File "/home/piyush/.virtualenvs/myenv/lib/python3.5/site-packages/django/core/management/commands/runserver.py", line 120, in inner_run
    self.check(display_num_errors=True)
  File "/home/piyush/.virtualenvs/myenv/lib/python3.5/site-packages/django/core/management/base.py", line 364, in check
    include_deployment_checks=include_deployment_checks,
  File "/home/piyush/.virtualenvs/myenv/lib/python3.5/site-packages/django/core/management/base.py", line 351, in _run_checks
    return checks.run_checks(**kwargs)
  File "/home/piyush/.virtualenvs/myenv/lib/python3.5/site-packages/django/core/checks/registry.py", line 73, in run_checks
    new_errors = check(app_configs=app_configs)
  File "/home/piyush/.virtualenvs/myenv/lib/python3.5/site-packages/django/core/checks/urls.py", line 13, in check_url_config
    return check_resolver(resolver)
  File "/home/piyush/.virtualenvs/myenv/lib/python3.5/site-packages/django/core/checks/urls.py", line 23, in check_resolver
    return check_method()
  File "/home/piyush/.virtualenvs/myenv/lib/python3.5/site-packages/django/urls/resolvers.py", line 397, in check
    for pattern in self.url_patterns:
  File "/home/piyush/.virtualenvs/myenv/lib/python3.5/site-packages/django/utils/functional.py", line 36, in __get__
    res = instance.__dict__[self.name] = self.func(instance)
  File "/home/piyush/.virtualenvs/myenv/lib/python3.5/site-packages/django/urls/resolvers.py", line 536, in url_patterns
    patterns = getattr(self.urlconf_module, "urlpatterns", self.urlconf_module)
  File "/home/piyush/.virtualenvs/myenv/lib/python3.5/site-packages/django/utils/functional.py", line 36, in __get__
    res = instance.__dict__[self.name] = self.func(instance)
  File "/home/piyush/.virtualenvs/myenv/lib/python3.5/site-packages/django/urls/resolvers.py", line 529, in urlconf_module
    return import_module(self.urlconf_name)
  File "/usr/lib/python3.5/importlib/__init__.py", line 126, in import_module
    return _bootstrap._gcd_import(name[level:], package, level)
  File "<frozen importlib._bootstrap>", line 986, in _gcd_import
  File "<frozen importlib._bootstrap>", line 969, in _find_and_load
  File "<frozen importlib._bootstrap>", line 958, in _find_and_load_unlocked
  File "<frozen importlib._bootstrap>", line 673, in _load_unlocked
  File "<frozen importlib._bootstrap_external>", line 665, in exec_module
  File "<frozen importlib._bootstrap>", line 222, in _call_with_frames_removed
  File "/home/piyush/Desktop/Djangosite/mysite/urls.py", line 16, in <module>
    from django.urls import include, url
ImportError: cannot import name 'url'

And mysite/urls.py :

from django.urls import include, url
from django.contrib import admin

urlpatterns = [
    url(r'^admin/', admin.site.urls),
    url(r'', include('blog.urls')),
]

And blog/urls.py :

from django.conf.urls import url
from . import views
urlpatterns = [
    url(r'^$', views.post_list, name='post_list'),
]

Sorry for the ginormous code, and the version of Django I am using here is 2.0.

But then I tried using the version used in tutorial that is 1.11.8, I got the same error but the last line of the error says ImportError: cannot import name 'include' instead of ImportError: cannot import name 'url'

I don't understand how to use the official documentation yet, I started Django yesterday itself. Sorry for the inconvenience.

Danke.

Body:
blog/urls.py:

from django.urls import path
from . import views
urlpatterns = [
  path(r'^$', views.post_list, name='post_list'),
]

mysite/urls.py:

from django.urls import include, path
from django.contrib import admin
urlpatterns = [
  path('admin/', admin.site.urls),
  path(r'', include('blog.urls')),
]

As suggested follow the documentation:

https://docs.djangoproject.com/en/2.0/intro/tutorial01/


问:
mapping through an array
I have an array called enquiries
Im trying to map through it like this:

```<code>enquiries.map(enquiry => enquiry.firstName)
</code>```

I get the following error:

```<code>TypeError: enquiries.map is not a function
</code>```

Here is output for console.log(enquiries)

```<code>{enquiries: Array(2)}
  enquiries:Array(2)
  0:{_id: "5a28364d5a36f4556af34741",
     firstName: "jonny",
     surname: "rotten",
     telephoneNumber: 859937,
     gender: ""male"", …}
  1:{id: "5a283e4c5a36f4556af34742",
     firstName: "bob",
     surname: "hoskins",
     telephoneNumber: 939483948,
     gender: "male", …}
</code>```

答:
Well, you have two reasons for that.
First, your "enquiries" variable is an object (but as it seems it's iteratable, that's good!).
As an object, it doesn't have the map function in its prototype.

Second, your arrow function uses curly brackets - which indicates that code should be written in.
To use the shortcut for a single value you could either rid of the curly brackets or use regular ones:

```<code>enquiries.map((enquiry) => (enquiry.firstName))
</code>```

or even:

```<code>enquiries.map(enquiry => enquiry.firstName)
</code>```

So for conclusion:

```<code>Array.from(enquiries).map(enquiry => enquiry.firstName)
</code>```

Title: mapping through an array

Body:
I have an array called enquiries
Im trying to map through it like this:

enquiries.map(enquiry => enquiry.firstName)

I get the following error:

TypeError: enquiries.map is not a function

Here is output for console.log(enquiries)

{enquiries: Array(2)}
  enquiries:Array(2)
  0:{_id: "5a28364d5a36f4556af34741",
     firstName: "jonny",
     surname: "rotten",
     telephoneNumber: 859937,
     gender: ""male"", …}
  1:{id: "5a283e4c5a36f4556af34742",
     firstName: "bob",
     surname: "hoskins",
     telephoneNumber: 939483948,
     gender: "male", …}

Body:
Well, you have two reasons for that.
First, your "enquiries" variable is an object (but as it seems it's iteratable, that's good!).
As an object, it doesn't have the map function in its prototype.

Second, your arrow function uses curly brackets - which indicates that code should be written in.
To use the shortcut for a single value you could either rid of the curly brackets or use regular ones:

enquiries.map((enquiry) => (enquiry.firstName))

or even:

enquiries.map(enquiry => enquiry.firstName)

So for conclusion:

Array.from(enquiries).map(enquiry => enquiry.firstName)


问:
How to take user input from api.ai(dialog flow) and store it in mysql database using php?

I want to take the user inputs through api.ai(dialog flow) chatbot and store it in MySQL database like asking the user details of name, age, contact num through chatbot and then the user details are to be stored in my database i.e.., MySQL database. After storing, at last, the chatbot should show the summary to the user for confirmation.

 I am able to interact and continue the conversation with my chatbot but unable to store the user's inputs.

I was searching at different places but I can't get the right process. I am comfortable with PHP to get the data from api.ai and to store in MySQL database.

Any help would be appreciated.
"Thanks in Advance!"

答:
You need to create a webhook to handle fulfillment from Dialogflow. This will be a publicly accessible URL that can take an HTTPS POST, with a JSON body. In your case, this will be your Heroku URL that you will enter into the Fulfillment tab in Dialogflow.

See https://dialogflow.com/docs/fulfillment for information about the JSON you are sent, the JSON you'll need to reply with, and other information you need to setup fulfillment.

In your PHP code - you can do whatever you want with the data being sent to you, including storing it in MySQL. You'll then send back JSON (see the above URL for the format) indicating how you want to reply to the user.


问:
copy specific text to copy another text in javascript
I want to make it like when i copy something i'll get something else on the clipboard.

```<code>document.addEventListener('copy', function(e){
  if(e.clipboardData.getData('Text').toString()==="@Trolluminati")
  e.clipboardData.setData ("Text", "<@325608201175433216>");
  e.preventDefault();
});
</code>```

Idk, how to make it so it will change the last copy of this specific text to the other one.

Right now it just keeps the same copied text and does not change it.

答:
 document.addEventListener('copy', function(e){
console.log(0);
if( window.getSelection().toString().trim()==="@Trolluminati")
  e.clipboardData.setData ("Text", "<@325608201175433216>");
else
  e.clipboardData.setData ("Text", window.getSelection().toString().trim());
e.preventDefault();
});```
</div>
</div>
</p>

<p>try this </p>

``` document.addEventListener('copy', function(e){
console.log(0);
if( window.getSelection().toString().trim()==="@Trolluminati")
  e.clipboardData.setData ("Text", "<@325608201175433216>");
else
  e.clipboardData.setData ("Text", window.getSelection().toString().trim());
e.preventDefault();
});
```

Body:
I want to make it like when i copy something i'll get something else on the clipboard.

document.addEventListener('copy', function(e){
  if(e.clipboardData.getData('Text').toString()==="@Trolluminati")
  e.clipboardData.setData ("Text", "<@325608201175433216>");
  e.preventDefault();
});

Idk, how to make it so it will change the last copy of this specific text to the other one.

Right now it just keeps the same copied text and does not change it.

Body:
 document.addEventListener('copy', function(e){
console.log(0);
if( window.getSelection().toString().trim()==="@Trolluminati")
  e.clipboardData.setData ("Text", "<@325608201175433216>");
else
  e.clipboardData.setData ("Text", window.getSelection().toString().trim());
e.preventDefault();
});

try this

 document.addEventListener('copy', function(e){
console.log(0);
if( window.getSelection().toString().trim()==="@Trolluminati")
  e.clipboardData.setData ("Text", "<@325608201175433216>");
else
  e.clipboardData.setData ("Text", window.getSelection().toString().trim());
e.preventDefault();
});


问:
Javascript serialized POST
I am trying to achieve that when I call the JS function, a post request is send. In my browser I would send:

```<code>http://myuser:password@hc2:80/api/callAction?deviceID=185&name=turnOn
</code>```

This works. Yet in my code it doesn't.

Important to note:
- Chrome does raise an Error: Request doesn't pass access control. If I disable this in Chrome, I doesn't display this error (yet no response from the server either).

```<code><script type="text/javascript">

    function changestate() {
        var http = new XMLHttpRequest();
        http.withCredentials = true;
        var user = "bassie"
        var pass = "password"
        var url = "http://hc2/api/callAction";
        var params = "deviceID=185&name=turnOff";
        http.open("POST", url, true);
        http.setRequestHeader("Authorization", "Basic " + user + ":" + pass);
        //Send the proper header information along with the request
        http.setRequestHeader("Content-type", "application/x-www-form-urlencoded");
        alert(http.responseText);
        http.onreadystatechange = function() {//Call a function when the state changes.
            if(http.readyState == 4 && http.status == 200) {
                    alert(http.responseText);
            }
        }
        http.send(params);
    }

</script>
</code>```

答:
The equivalent to putting the URL in the browser's location is a GET request, not POST.

Since you're sending a cross-domain request, you won't be able to read the response (unless you relay through a proxy on your origin server). So you can't read http.responseText, and can simply omit the onreadystatechange function; you'll just have to assume it

```<code>function changestate() {
    var http = new XMLHttpRequest();
    http.withCredentials = true;
    var user = "bassie"
    var pass = "password"
    var url = "http://hc2/api/callAction";
    var params = "deviceID=185&name=turnOff";
    http.open("GET", url + "?" + params, true);
    http.setRequestHeader("Authorization", "Basic " + user + ":" + pass);
    http.send();
}
</code>```

Title: Javascript serialized POST

Body:
I am trying to achieve that when I call the JS function, a post request is send. In my browser I would send:

http://myuser:password@hc2:80/api/callAction?deviceID=185&name=turnOn

This works. Yet in my code it doesn't.

Important to note:
- Chrome does raise an Error: Request doesn't pass access control. If I disable this in Chrome, I doesn't display this error (yet no response from the server either).

Body:
The equivalent to putting the URL in the browser's location is a GET request, not POST.

Since you're sending a cross-domain request, you won't be able to read the response (unless you relay through a proxy on your origin server). So you can't read http.responseText, and can simply omit the onreadystatechange function; you'll just have to assume it

function changestate() {
    var http = new XMLHttpRequest();
    http.withCredentials = true;
    var user = "bassie"
    var pass = "password"
    var url = "http://hc2/api/callAction";
    var params = "deviceID=185&name=turnOff";
    http.open("GET", url + "?" + params, true);
    http.setRequestHeader("Authorization", "Basic " + user + ":" + pass);
    http.send();
}


问:
How to link a web page to another web page using which has accordion page using events and jquery
I have a index page where I want to link to a case studies pages (html) which has an accordion jquery menu nav bar driven by event triggers.

How do I link these from the index page so that when a user clicks on it will take you to the case studies page opening the relevant page in the accordion jquery section.

I have tried using this code, on the index page but this does not work.

a href="church-clock-case-studies.html" onClick="$('#test3Handle0').click()">Lyndon Hall

答:
You will need to pass an argument to the target page. The easiest way to do so would be using the hash, i.e. make your href point to church-clock-case-studies.html#yourpage.

In church-clock-case-studies.html you'll then use location.hash to set the default page of the accordion. For instructions on how to set the default accordion page, have a look at the jQuery UI Accordion help.

ContentLicense: CC BY-SA 2.5


问:
What's the most effective way to update my remote repository after changing it locally?
So I've pushed my Django project to Bitbucket. However after I make changes to it offline, I want to update the remote repo. How would I go about this?</p>

答:
As it seems git push will do the job, if you have commited your changes.

But I am pretty sure you want to read up on git before you touch a remote repo.

git cheat sheet

Official git reference

Pro Git, the one and only book, pdf


问:
gridview not refreshing on postback C# asp.net
I'm new to asking on stack overflow so go easy on me plz.

Using VS2012 premium edition and web forms 4.5.2.

I'm trying to update the gridview on postback.. (it updates when redirects)
and I've searched a lot of articles but it only says about databind..
actually I am not databinding
I'm using the selectmethod

```<code><asp:GridView runat="server" ID="someGrid" CellPadding="10"
    DataKeyNames="idx" AutoGenerateColumns="true"
    selectMethod="someGrid_GetData" ItemType="orders">
</asp:GridView>
</code>```

and I am just calling the selectMethod on the behind.

```<code>    public IQueryable someGrid_GetData()
    {
        someContext soc = new someContext();
        var item = order.Where(s => s.idx == s.idx);
        return item;
    }
</code>```

It works like a charm, but one thing..
It doesn't update when postback.
I put

```<code>someGrid.databind()
</code>```

on page_load() !ispostback but it doesn't work.

It seems like an easy fix, but I've been struggling with this for hours.

Thanks in advance.

答:
Oh I got it.

@AluanHaddad was right. the data binding was working.
I just thought it was not working because it didn't update when I input
one data even though I was updating to the database.

But when I input two data consequently it showed the former data.. not the most frequent one.

The thing was, I actually had an onclick button which had a update button
and I think.. (I'm not sure actually how.. )

but the page_load event is fired before the onclick button,
thus the data was not uploaded then.

I moved the someGrid.databind() to the onclick button in the last and it works!!

Thanks Aluan!!

Body:
I'm new to asking on stack overflow so go easy on me plz.

Using VS2012 premium edition and web forms 4.5.2.

I'm trying to update the gridview on postback.. (it updates when redirects)
and I've searched a lot of articles but it only says about databind..
actually I am not databinding
I'm using the selectmethod

<asp:GridView runat="server" ID="someGrid" CellPadding="10"
    DataKeyNames="idx" AutoGenerateColumns="true"
    selectMethod="someGrid_GetData" ItemType="orders">
</asp:GridView>

and I am just calling the selectMethod on the behind.

    public IQueryable someGrid_GetData()
    {
        someContext soc = new someContext();
        var item = order.Where(s => s.idx == s.idx);
        return item;
    }

It works like a charm, but one thing..
It doesn't update when postback.
I put

someGrid.databind()

on page_load() !ispostback but it doesn't work.

It seems like an easy fix, but I've been struggling with this for hours.

Thanks in advance.


问:
How to sum this this JSON data values below
I am Imran, Please see the JSON data below and tell how to sum the result below:

```<code>{
    "_id" : "59ae33130d83f7001112ea5e",
    "user" : {
        "passcode" : "****",
        "operator" : "premiereservices",
        "phone_num" : "8587366808",
        "balance" : 1.99
    },
    "location_id" : "58ec4e82addba5001160fc07",
    "customer_id" : "premiereservices",
    "shopping_cart" : {
        "item_count" : 2,
        "items" : [
            {
                "customer_id" : "premiereservices",
                "barcode" : "666",
                "description" : "Test Item",
                "price" : 1.99,
                "taxable" : false,
                "fixed_price_tax" : 0.05,
                "__v" : 0
            },
            {
                "customer_id" : "premiereservices",
                "barcode" : "777",
                "description" : "Test Item",
                "price" : 1.99,
                "taxable" : false,
                "fixed_price_tax" : 0.05,
                "__v" : 0
            }
        ],
        "total_due" : 4.08
    },
    "cart_total" : 4.08,
    "Date" : "2017-09-04T23:16:03-06:00",
    "__v" : 0
}
{
    "_id" : "59ae38f186c7e70011a4e224",
    "location_id" : "58ec522a99da86001123ec47",
    "customer_id" : "premiereservices",
    "user" : {
        "phone_num" : "4129514766",
        "balance" : 0.03
    },
    "cart_total" : 3.55,
    "shopping_cart" : {
        "items" : [
            {
                "barcode" : "028400020008",
                "description" : "Smartfood White Cheddar Popcorn",
                "price" : 1.25,
                "taxable" : false,
                "tax_collected" : 0
            },
            {
                "barcode" : "012000286209",
                "description" : "Pureleaf Unsweetened Tea",
                "price" : 2.3,
                "taxable" : false,
                "tax_collected" : 0
            }
        ]
    },
    "Date" : "2017-09-04T23:41:05-06:00",
    "__v" : 0
}
{
    "_id" : "59ae3c569ce8ec00112775d6",
    "location_id" : "58ec522a99da86001123ec47",
    "customer_id" : "premiereservices",
    "user" : {
        "phone_num" : "7192358363",
        "balance" : 9.99
    },
    "cart_total" : 3.25,
    "shopping_cart" : {
        "items" : [
            {
                "barcode" : "10002",
                "description" : "GnG Burrito Bacon/GChili/Potato",
                "price" : 3.25,
                "taxable" : true,
                "tax_collected" : 0
            }
        ]
    },
    "Date" : "2017-09-04T23:55:34-06:00",
    "__v" : 0
}
</code>```

Result Will be

cart_total: 10.88

price: 10.78

tax_collected: 0

Please anybody help me what will be the aggregate query functions

答:
There you go:</p>

```<code>db.collection.aggregate({
    $project: { // for each document...
        cart_total: 1, // ...keep the "cart_total" field
        price: { $sum: "$shopping_cart.items.price" }, // ...sum up all "price" fields
        tax_collected: { $sum: "$shopping_cart.items.tax_collected" } // ...sum up all "price" fields
    }
}, {
    $group: {
        _id: null, // group all documents into the same bucket and sum up a bunch of values
        cart_total: { $sum: "$cart_total" },
        price: { $sum: "$price" },
        tax_collected: { $sum: "$tax_collected" }
    }
}, {
    $project: {
        _id: 0 // remove the "_id" field
    }
})
</code>```

Body:
I am Imran, Please see the JSON data below and tell how to sum the result below:

{
    "_id" : "59ae33130d83f7001112ea5e",
    "user" : {
        "passcode" : "****",
        "operator" : "premiereservices",
        "phone_num" : "8587366808",
        "balance" : 1.99
    },
    "location_id" : "58ec4e82addba5001160fc07",
    "customer_id" : "premiereservices",
    "shopping_cart" : {
        "item_count" : 2,
        "items" : [
            {
                "customer_id" : "premiereservices",
                "barcode" : "666",
                "description" : "Test Item",
                "price" : 1.99,
                "taxable" : false,
                "fixed_price_tax" : 0.05,
                "__v" : 0
            },
            {
                "customer_id" : "premiereservices",
                "barcode" : "777",
                "description" : "Test Item",
                "price" : 1.99,
                "taxable" : false,
                "fixed_price_tax" : 0.05,
                "__v" : 0
            }
        ],
        "total_due" : 4.08
    },
    "cart_total" : 4.08,
    "Date" : "2017-09-04T23:16:03-06:00",
    "__v" : 0
}
{
    "_id" : "59ae38f186c7e70011a4e224",
    "location_id" : "58ec522a99da86001123ec47",
    "customer_id" : "premiereservices",
    "user" : {
        "phone_num" : "4129514766",
        "balance" : 0.03
    },
    "cart_total" : 3.55,
    "shopping_cart" : {
        "items" : [
            {
                "barcode" : "028400020008",
                "description" : "Smartfood White Cheddar Popcorn",
                "price" : 1.25,
                "taxable" : false,
                "tax_collected" : 0
            },
            {
                "barcode" : "012000286209",
                "description" : "Pureleaf Unsweetened Tea",
                "price" : 2.3,
                "taxable" : false,
                "tax_collected" : 0
            }
        ]
    },
    "Date" : "2017-09-04T23:41:05-06:00",
    "__v" : 0
}
{
    "_id" : "59ae3c569ce8ec00112775d6",
    "location_id" : "58ec522a99da86001123ec47",
    "customer_id" : "premiereservices",
    "user" : {
        "phone_num" : "7192358363",
        "balance" : 9.99
    },
    "cart_total" : 3.25,
    "shopping_cart" : {
        "items" : [
            {
                "barcode" : "10002",
                "description" : "GnG Burrito Bacon/GChili/Potato",
                "price" : 3.25,
                "taxable" : true,
                "tax_collected" : 0
            }
        ]
    },
    "Date" : "2017-09-04T23:55:34-06:00",
    "__v" : 0
}

Result Will be

cart_total: 10.88

price: 10.78

tax_collected: 0

Please anybody help me what will be the aggregate query functions

Body:
There you go:

db.collection.aggregate({
    $project: { // for each document...
        cart_total: 1, // ...keep the "cart_total" field
        price: { $sum: "$shopping_cart.items.price" }, // ...sum up all "price" fields
        tax_collected: { $sum: "$shopping_cart.items.tax_collected" } // ...sum up all "price" fields
    }
}, {
    $group: {
        _id: null, // group all documents into the same bucket and sum up a bunch of values
        cart_total: { $sum: "$cart_total" },
        price: { $sum: "$price" },
        tax_collected: { $sum: "$tax_collected" }
    }
}, {
    $project: {
        _id: 0 // remove the "_id" field
    }
})


问:
Get logged windows user in NodeJS
My NodeJS app is running on a Linux server.

I have several machines connecting to the app (via browser) running on Windows.

How can I get, in my app, the current windows logged in user?

答:
If your Linux server is on the same local network as your Windows clients, you can use an NTLM authentication package like express-ntlm to authenticate your users and get their username.

```<code>var express = require('express'),
ntlm = require('express-ntlm');

var app = express();

app.use(ntlm({
    debug: function() {
        var args = Array.prototype.slice.apply(arguments);
        console.log.apply(null, args);
    },
    domain: 'MYDOMAIN',
    domaincontroller: 'ldap://myad.example',
}));

app.all('*', function(request, response) {
    response.end(JSON.stringify(request.ntlm)); // {"DomainName":"MYDOMAIN","UserName":"MYUSER","Workstation":"MYWORKSTATION"}
});

app.listen(80);
</code>```

Title: Get logged windows user in NodeJS

Body:
If your Linux server is on the same local network as your Windows clients, you can use an NTLM authentication package like express-ntlm to authenticate your users and get their username.

var express = require('express'),
ntlm = require('express-ntlm');

var app = express();

app.use(ntlm({
    debug: function() {
        var args = Array.prototype.slice.apply(arguments);
        console.log.apply(null, args);
    },
    domain: 'MYDOMAIN',
    domaincontroller: 'ldap://myad.example',
}));

app.all('*', function(request, response) {
    response.end(JSON.stringify(request.ntlm)); // {"DomainName":"MYDOMAIN","UserName":"MYUSER","Workstation":"MYWORKSTATION"}
});

app.listen(80);


问:
Recursive function returning bool
I can't understand the following recursive function returning a  bool.

```<code> bool p(int a[], int inf, int sup) {
    if(sup==inf)
        if(a[inf]>0)
            return true;
        else
            return false;
    int m=(inf+sup)/2;
    bool left=p(a,inf,m);
    bool right=p(a,m+1,sup);
    return left && right;
}
</code>```

It should return true if all the elements in (inf, sup) are positive or else false, but I can't see how the elements that are not at the two extrema of the array are considered, since the condition that is tested is that a[inf]>0 and inf is not changed during recursion for left, while it gets values only in the right part of the array for right.

So basically, since it is different from easier recursions (like the factorial or Fibonacci) I can't understand how recursion works here: I tried to start from the base case and go on to the second step but it is unclear how to proceed.

Can anyone suggest me how to follow the recursion in this case to understand how this function returns true iff all the elements in (inf, sup) are positive?

答:
It is always useful to try run the code for small arrays and then notice a pattern.

Algorithm definitely works for sup = inf (size = 1) and sup = inf + 1 (size = 2);

Consequently it works for size = 3, because you split your array in two (sizes 2 and 1) and we have already established that the code works for these smaller sizes.

Basically, if you know that Algo (size = k) and Algo (size = k - 1) return correct results then so do Algo (size = 2k = k + k) and Algo (size = 2k - 1 = k + (k - 1)).

And so Algo (n) works for every n.

This near-mathematical reasoning is called "mathematical induction".

Title: Recursive function returning bool

Body:
I can't understand the following recursive function returning a  bool.

 bool p(int a[], int inf, int sup) {
    if(sup==inf)
        if(a[inf]>0)
            return true;
        else
            return false;
    int m=(inf+sup)/2;
    bool left=p(a,inf,m);
    bool right=p(a,m+1,sup);
    return left && right;
}

It should return true if all the elements in (inf, sup) are positive or else false, but I can't see how the elements that are not at the two extrema of the array are considered, since the condition that is tested is that a[inf]>0 and inf is not changed during recursion for left, while it gets values only in the right part of the array for right.

So basically, since it is different from easier recursions (like the factorial or Fibonacci) I can't understand how recursion works here: I tried to start from the base case and go on to the second step but it is unclear how to proceed.

Can anyone suggest me how to follow the recursion in this case to understand how this function returns true iff all the elements in (inf, sup) are positive?


问:
HTML position = center-10px
I am making an HTML website. I have an map as an background picture and I need a button to be on the map. Like this:

But when I start scaling my site, this happens:
I know what is the problem: the position of the pictures is center-X px and center-Y px, not margin: 40% 0px 0px 20%. But how to do that? position: absolute; left: 50%-30px; top: 50%-83px doesn´t work.

答:
You will want the image to be relative to the background image (div container with the map as background image):</p>

```<code>position: relative; left: 30px; top: 83px
</code>```

Title: HTML position = center-10px

Body:
You will want the image to be relative to the background image (div container with the map as background image):

position: relative; left: 30px; top: 83px


问:
Setting a TextBox's .Text to "" or String.Empty does not show up in the GUI. Do I have to force a GUI update?
Code is running on main thread.
Code is:

```<code>OutputBox.Text = String.Empty; //have tried ""
var auto = new AutoGram(PrefixBox.Text, (int)SeedNum.Value,
     CharsBox.Text.ToCharArray(), SuffixBox.Text); //Just something for my program.
OutputBox.Text = auto.Execute(); //Again shouldn't be relevant.
</code>```

This code runs on click of a "Run" button. I want the outputbox to be cleared upon this button's click and then filled in when the code executes.
I have placed breakpoints everywhere and the text setting does show up as edited in visual studio, but not in the application's GUI. Thanks so much.

答:
You're assigning an empty string to the output and immediately overwriting the empty string by using auto.Execute().

An alternative approach would be to have two buttons, one specifically to clear all fields, and one to execute your method.

```<code>string output = "foo";

private void btnRun_Click(object sender, EventArgs e)
{
    textBox1.Text = output;
}

private void btnClear_Click(object sender, EventArgs e)
{
    textBox1.Text = "";
}
</code>```

Body:
Code is running on main thread.
Code is:

OutputBox.Text = String.Empty; //have tried ""
var auto = new AutoGram(PrefixBox.Text, (int)SeedNum.Value,
     CharsBox.Text.ToCharArray(), SuffixBox.Text); //Just something for my program.
OutputBox.Text = auto.Execute(); //Again shouldn't be relevant.

This code runs on click of a "Run" button. I want the outputbox to be cleared upon this button's click and then filled in when the code executes.
I have placed breakpoints everywhere and the text setting does show up as edited in visual studio, but not in the application's GUI. Thanks so much.

Body:
You're assigning an empty string to the output and immediately overwriting the empty string by using auto.Execute().

An alternative approach would be to have two buttons, one specifically to clear all fields, and one to execute your method.

string output = "foo";

private void btnRun_Click(object sender, EventArgs e)
{
    textBox1.Text = output;
}

private void btnClear_Click(object sender, EventArgs e)
{
    textBox1.Text = "";
}


问:
How to properly stringify XML to put in JSON body as part of CURL command
I'd like to add an XML file to my JSON request body in a CURL command. This is what I have right now

```<code>data=$(cat testdata.xml)
curl -X POST --header 'Content-Type: application/json' --header 'Accept: application/json' -d '{ "data": ''"$(echo $data)"''}' 'http://...'
</code>```

However this sends { data: '$(echo $data)' } } instead of actually fetching the file.

If I take out the outer single quotes around $(echo $data) like this:

```<code>data=$(cat testdata.xml)
curl -X POST --header 'Content-Type: application/json' --header 'Accept: application/json' -d '{ "data": '"$(echo $data)"'}' 'http://...'
</code>```

Then I encounter error Unexpected token < in JSON at position 10

because the server is reading

```<code>'{ "data": <?xml version="1.0" encoding="UTF-8"?> - <note> <to>Tove</to> <from>Jani</from> <heading>Reminder</heading> <body>Don\'t forget me this weekend!</body> </note>}',
</code>```

How do I fix this? Note that I do NOT want to convert to JSON first. I only want to send the stringified xml file in body.

答:
As always, use jq to generate properly-escaped JSON in bash.

```<code>data=$(jq -Rs '{ "data": . }' <testdata.xml)
curl -X POST \
     --header 'Content-Type: application/json' \
     --header 'Accept: application/json' \
     -d "$data" \
     "$url"
</code>```

If you don't have jq installed, you can use Python for the job, substituting the following for the line that used jq:

```<code>data=$(python -c 'import json, sys; json.dump({"data": sys.stdin.read()}, sys.stdout)' <testdata.xml)
</code>```

Body:
I'd like to add an XML file to my JSON request body in a CURL command. This is what I have right now

data=$(cat testdata.xml)
curl -X POST --header 'Content-Type: application/json' --header 'Accept: application/json' -d '{ "data": ''"$(echo $data)"''}' 'http://...'

However this sends { data: '$(echo $data)' } } instead of actually fetching the file.

If I take out the outer single quotes around $(echo $data) like this:

data=$(cat testdata.xml)
curl -X POST --header 'Content-Type: application/json' --header 'Accept: application/json' -d '{ "data": '"$(echo $data)"'}' 'http://...'

Then I encounter error Unexpected token < in JSON at position 10

because the server is reading

'{ "data": <?xml version="1.0" encoding="UTF-8"?> - <note> <to>Tove</to> <from>Jani</from> <heading>Reminder</heading> Don\'t forget me this weekend! </note>}',

How do I fix this? Note that I do NOT want to convert to JSON first. I only want to send the stringified xml file in body.

Body:
As always, use jq to generate properly-escaped JSON in bash.

data=$(jq -Rs '{ "data": . }' <testdata.xml)
curl -X POST \
     --header 'Content-Type: application/json' \
     --header 'Accept: application/json' \
     -d "$data" \
     "$url"

If you don't have jq installed, you can use Python for the job, substituting the following for the line that used jq:

data=$(python -c 'import json, sys; json.dump({"data": sys.stdin.read()}, sys.stdout)' <testdata.xml)


问:
I want to average the numbers inputted into the ArrayList, however it only calculates average as 0.0
It sums perfectly fine, however it averages the values as 0.0 no matter what numbers I input. How can I make it output the correct average value?</p>

```<code>import java.util.Scanner;
import java.io.*;
import java.util.ArrayList;

public class Calculation {
    static Toolkit tools = new Toolkit();
    public static void main(String [] args) {
        double average = 0.0;
        double sum = 0;
        int i =0;
        int num = 0;
        int nElements = 0;
        Scanner console = new Scanner(System.in);

        ArrayList<Integer> value = new ArrayList<Integer>();
        System.out.println("Enter value: ");
        num = console.nextInt();
        while(num != 0) {
            value.add(new Integer(num));
        }

        for(i = 0; i < value.size(); i++) {
             sum += value.size();
        }
        average = sum / value.size();
        System.out.println("Number of values read: " + value.size());
        System.out.println("Values sum: " + sum + "Values average: " +
                            average);
        System.exit(0);
    }
}
</code>```

答:
What Aomine said is correct. However, it is not the only problem. Inside your for loop you are adding the size of your Arraylist to your 'sum' variable. You should be adding the values it holds instead, i.e. summing them.</p>

```<code>double average = 0.0;
    double sum = 0;
    int i = 0;
    int num = 0;

    Scanner console = new Scanner(System.in);

    ArrayList<Integer> value = new ArrayList<Integer>();
    System.out.println("Enter value: ");
    num = console.nextInt();
    while (num != 0) {
        value.add(new Integer(num));
        num = console.nextInt();
    }

    for (i = 0; i < value.size(); i++) {
        sum += value.get(i);
    }
    average = sum / value.size();
    System.out.println("Number of values read: " + value.size());
    System.out.println("Values sum: " + sum + "Values average: " + average);
</code>```

Body:
It sums perfectly fine, however it averages the values as 0.0 no matter what numbers I input. How can I make it output the correct average value?

import java.util.Scanner;
import java.io.*;
import java.util.ArrayList;

public class Calculation {
    static Toolkit tools = new Toolkit();
    public static void main(String [] args) {
        double average = 0.0;
        double sum = 0;
        int i =0;
        int num = 0;
        int nElements = 0;
        Scanner console = new Scanner(System.in);

        ArrayList<Integer> value = new ArrayList<Integer>();
        System.out.println("Enter value: ");
        num = console.nextInt();
        while(num != 0) {
            value.add(new Integer(num));
        }

        for(i = 0; i < value.size(); i++) {
             sum += value.size();
        }
        average = sum / value.size();
        System.out.println("Number of values read: " + value.size());
        System.out.println("Values sum: " + sum + "Values average: " +
                            average);
        System.exit(0);
    }
}

Body:
What Aomine said is correct. However, it is not the only problem. Inside your for loop you are adding the size of your Arraylist to your 'sum' variable. You should be adding the values it holds instead, i.e. summing them.

double average = 0.0;
    double sum = 0;
    int i = 0;
    int num = 0;

    Scanner console = new Scanner(System.in);

    ArrayList<Integer> value = new ArrayList<Integer>();
    System.out.println("Enter value: ");
    num = console.nextInt();
    while (num != 0) {
        value.add(new Integer(num));
        num = console.nextInt();
    }

    for (i = 0; i < value.size(); i++) {
        sum += value.get(i);
    }
    average = sum / value.size();
    System.out.println("Number of values read: " + value.size());
    System.out.println("Values sum: " + sum + "Values average: " + average);


问:
Keep only unique lines (names) from text file and remove the rest
I have a list of names in a text file like below :

```<code>John Doe
John Doe
Carl Berg
Roger Hansen
Roger Hansen
Jane Doe
Lance Anderson
Lance Anderson
</code>```

I only want to keep the unique names (the whole line) and remove the rest.
How do I do this with a batch script or maybe Notepad++?

答:
In Notepad++, do a Find and Replace:

Find what:

```<code>(.+)[\r\n]+(\1[\r\n]*)+
</code>```

Replace it with nothing.

Be sure to select "Regular expression" when doing the Find and Replace, and uncheck the box for ". matches newline".

What if there are duplicate lines in the file that aren't grouped together?

Before doing the Find and Replace, do: Edit -> Line Operations -> Sort Lines Lexicographically Ascending

ContentLicense: CC BY-SA 4.0

Body:
I have a list of names in a text file like below :

John Doe
John Doe
Carl Berg
Roger Hansen
Roger Hansen
Jane Doe
Lance Anderson
Lance Anderson

I only want to keep the unique names (the whole line) and remove the rest.
How do I do this with a batch script or maybe Notepad++?

Body:
In Notepad++, do a Find and Replace:

Find what:

(.+)[\r\n]+(\1[\r\n]*)+

Replace it with nothing.

Be sure to select "Regular expression" when doing the Find and Replace, and uncheck the box for ". matches newline".

What if there are duplicate lines in the file that aren't grouped together?

Before doing the Find and Replace, do: Edit -> Line Operations -> Sort Lines Lexicographically Ascending


问:
Python Dataframe: operation per row in all dataframe
I have a dataframe in python which looks like the following. The number of column is not fix and increase with a iteration but follows the same pattern (more columns can be added), but to illustrate my issue please take my current df.

```<code>           0          1          2
0   0.414143  13.898568  17.322851
1  24.348445   0.514046  36.663878
0   0.776340  11.541118  21.312318
0  11.700539  56.442854  22.853285
2  11.599149  12.977594   4.905724
1  22.944083   0.501586  42.553746
0   4.092333  39.273114  23.406309
</code>```

The idea is to keep the same dataframe dimension and compute the min value per row and set zero to the others values in the same row. The final results should look like this:

```<code>           0          1          2
0   0.414143          0          0
1          0   0.514046          0
0   0.776340          0          0
0  11.700539          0          0
2          0          0   4.905724
1          0   0.501586          0
0   4.092333          0          0
</code>```

I set the row and column index with the same format (This indexes are integers in a range between [0-9]. The value which match with the same index, is the value that I'd like to keep it and set others as zeros.

Any idea which function could I apply to achieve my goal?

Thanks for your help.

Body:
I have a dataframe in python which looks like the following. The number of column is not fix and increase with a iteration but follows the same pattern (more columns can be added), but to illustrate my issue please take my current df.

           0          1          2
0   0.414143  13.898568  17.322851
1  24.348445   0.514046  36.663878
0   0.776340  11.541118  21.312318
0  11.700539  56.442854  22.853285
2  11.599149  12.977594   4.905724
1  22.944083   0.501586  42.553746
0   4.092333  39.273114  23.406309

The idea is to keep the same dataframe dimension and compute the min value per row and set zero to the others values in the same row. The final results should look like this:

           0          1          2
0   0.414143          0          0
1          0   0.514046          0
0   0.776340          0          0
0  11.700539          0          0
2          0          0   4.905724
1          0   0.501586          0
0   4.092333          0          0

I set the row and column index with the same format (This indexes are integers in a range between [0-9]. The value which match with the same index, is the value that I'd like to keep it and set others as zeros.

Any idea which function could I apply to achieve my goal?

Thanks for your help.

Body:
UPDATE: same answer applied to an updated DF:

In [134]: df
Out[134]:
           0          1          2
0   0.414143  13.898568  17.322851
1  24.348445   0.514046  36.663878
0   0.776340  11.541118  21.312318
0  11.700539  56.442854  22.853285
2  11.599149  12.977594   4.905724
1  22.944083   0.501586  42.553746
0   4.092333  39.273114  23.406309

In [135]: df[~df.eq(df.min(1), axis=0)] = 0

In [136]: df
Out[136]:
           0         1         2
0   0.414143  0.000000  0.000000
1   0.000000  0.514046  0.000000
0   0.776340  0.000000  0.000000
0  11.700539  0.000000  0.000000
2   0.000000  0.000000  4.905724
1   0.000000  0.501586  0.000000
0   4.092333  0.000000  0.000000

Old answer:

One of many possible ways:

In [108]: df[~df.eq(df.min(1), axis=0)] = 0

In [109]: df
Out[109]:
           0          1
1   0.000000  13.898568
1   0.000000   0.514046
1   0.000000  11.541118
1   0.000000  12.977594
0  11.700539   0.000000
1   0.000000   0.501586
0   2.358687   0.000000
0  22.853285   0.000000


问:
Really strange behaviour in android firebaseRecycleAdapter checkbox
I have to following problem:

I have a FirebaseRecyclerAdapter with checkboxes. It looks like this:

Everything works fine until you click two checkboxes in a row. In this case: if I where to click on listItem 3 this happends:

It duplicates the behaviour in the previous checked listitem

The code that is responsible for this is:

```<code>            listCheckbox.setOnClickListener(new View.OnClickListener() {
                            @Override
                            public void onClick(View v) {
                                Log.i("boxPosition", String.valueOf(position));
                                if (listCheckbox.isChecked()) {
                                    Log.i("boxChecked", String.valueOf(position));
                                    mDoneListRef.child(list_id).child("ticked").setValue(Boolean.valueOf(true));
                                } else if (!listCheckbox.isChecked()){
                                        Log.i("boxChecked", String.valueOf(position));
                                        mDoneListRef.child(list_id).child("ticked").setValue(Boolean.valueOf(false));
                                }

                            }
                        });
</code>```

I found it only happens when the else if statement is added. If I only work with the logic of the if statement everything works like expected (execpt that you cant set the value false because the missing else if / else statement).

I assume there is something wrong with the else if statement. So again, the problem only happends when the else if statement is added and you tick two checkboxes to the same value in a row.

Am I perhaps missing something to break to loop or something that resets the value of the position?

edit : I tried different onclickevents, creating and calling another databaseRefference in the clickevent. Neither of these called things seem to work. They all give the same result

答: After a lot of researching I found out that after the box is ticked, it should call         notifyItemChanged(position); instead of notifyDataSetChanged();

Body:
I have to following problem:

I have a FirebaseRecyclerAdapter with checkboxes. It looks like this:

Everything works fine until you click two checkboxes in a row. In this case: if I where to click on listItem 3 this happends:

It duplicates the behaviour in the previous checked listitem

The code that is responsible for this is:

            listCheckbox.setOnClickListener(new View.OnClickListener() {
                            @Override
                            public void onClick(View v) {
                                Log.i("boxPosition", String.valueOf(position));
                                if (listCheckbox.isChecked()) {
                                    Log.i("boxChecked", String.valueOf(position));
                                    mDoneListRef.child(list_id).child("ticked").setValue(Boolean.valueOf(true));
                                } else if (!listCheckbox.isChecked()){
                                        Log.i("boxChecked", String.valueOf(position));
                                        mDoneListRef.child(list_id).child("ticked").setValue(Boolean.valueOf(false));
                                }

                            }
                        });

I found it only happens when the else if statement is added. If I only work with the logic of the if statement everything works like expected (execpt that you cant set the value false because the missing else if / else statement).

I assume there is something wrong with the else if statement. So again, the problem only happends when the else if statement is added and you tick two checkboxes to the same value in a row.

Am I perhaps missing something to break to loop or something that resets the value of the position?

edit : I tried different onclickevents, creating and calling another databaseRefference in the clickevent. Neither of these called things seem to work. They all give the same result


问:
Use string + variable + string on document.write
i need to show on a window.open, a document.write with 2 strings and a variable on it, but the problem is that the variable is not showing and in the place of it , it shows : [object window], here is my code, thanks. </p>

```<code>function showResult() {
    var result = parseInt(valor1 + valor2 + valor3 + valor4 + valor5 +
        valor6 + valor7 + valor8 + valor9 + valor10 + valor11 + valor12 +
        valor13 + valor14 + valor15 + valor16 + valor17 + valor18 +
        valor19 + valor20 + valor21 + valor22 + valor23 + valor24 +
        valor25 + valor26 + valor27 + valor28 + valor29 + valor30 +
        valor31 + valor32 + valor33 + valor34 + valor35 + valor36 +
        valor37 + valor38 + valor39 + valor40 + valor41 + valor42 +
        valor43 + valor44 + valor45);
    if (result >= 0 && result <= 20) {
        result = window.open("", "respuesta1",
            "left=150,top=150,height=300,width=500");
        result.document.write("<b> Su resultado es:</b> " + result +
            " <br> <b> Muy Bajo </b> :  Con esta puntuación debes saber que todavía no conoces suficientemente qué emociones son las que vives, no valoras adecuadamente tus capacidades, que es seguro que las tienes. Son muchas las habilidades que no pones en práctica, y son necesarias para que te sientas más a gusto contigo mismo y las relaciones con la gente sean satisfactorias. Este programa de trabajo te podrá ser de gran ayuda para aumentar tus habilidades de inteligencia emocional."
        );
    }
</code>```

答:
Try this, works for me.
I noticed that you are changing result to a new window, this then takes away the value. Therefore, make a new variable called newResult, give it the value of result, then pass it through and it works. :)</p>

```<code>var valor1 = 2;
var valor2 = 6;
var valor3 = 10;
function showResult() {

    var result = parseInt(valor1 + valor2 + valor3);
    console.log(result);

    if (result >= 0 && result <= 20) {
        newResult = result;
     result = window.open();

       result.document.write("<b> Su resultado es:</b> " + newResult +
         " <br> <b> Muy Bajo </b> :  Con esta puntuación debes saber que"
      );
   }
}

showResult();
</code>```

Body:
i need to show on a window.open, a document.write with 2 strings and a variable on it, but the problem is that the variable is not showing and in the place of it , it shows : [object window], here is my code, thanks.

function showResult() {
    var result = parseInt(valor1 + valor2 + valor3 + valor4 + valor5 +
        valor6 + valor7 + valor8 + valor9 + valor10 + valor11 + valor12 +
        valor13 + valor14 + valor15 + valor16 + valor17 + valor18 +
        valor19 + valor20 + valor21 + valor22 + valor23 + valor24 +
        valor25 + valor26 + valor27 + valor28 + valor29 + valor30 +
        valor31 + valor32 + valor33 + valor34 + valor35 + valor36 +
        valor37 + valor38 + valor39 + valor40 + valor41 + valor42 +
        valor43 + valor44 + valor45);
    if (result >= 0 && result <= 20) {
        result = window.open("", "respuesta1",
            "left=150,top=150,height=300,width=500");
        result.document.write(" Su resultado es: " + result +
            "
  Muy Bajo  :  Con esta puntuación debes saber que todavía no conoces suficientemente qué emociones son las que vives, no valoras adecuadamente tus capacidades, que es seguro que las tienes. Son muchas las habilidades que no pones en práctica, y son necesarias para que te sientas más a gusto contigo mismo y las relaciones con la gente sean satisfactorias. Este programa de trabajo te podrá ser de gran ayuda para aumentar tus habilidades de inteligencia emocional."
        );
    }

Body:
Try this, works for me.
I noticed that you are changing result to a new window, this then takes away the value. Therefore, make a new variable called newResult, give it the value of result, then pass it through and it works. :)

var valor1 = 2;
var valor2 = 6;
var valor3 = 10;
function showResult() {

    var result = parseInt(valor1 + valor2 + valor3);
    console.log(result);

    if (result >= 0 && result <= 20) {
        newResult = result;
     result = window.open();

       result.document.write("<b> Su resultado es:</b> " + newResult +
         " <br> <b> Muy Bajo </b> :  Con esta puntuación debes saber que"
      );
   }
}

showResult();


问:
AWS - is there a way to receive events asynchronously without the lambda
I have a general AWS question. I have started using AWS sdk, but looks like if I want to receive events asynchronously from AWS(ex: cloudwatch events), lambda functions is the only way. I want to write a simple application that registers a callback to AWS for events, but i couldn't find a way to do that till now, since i don't want to use lambda, i have been polling from my application. Please, let me know if polling is the only option or if there is a better way to resolve it without polling.</p>

答:
From the documentation:

You can configure the following AWS services as targets for CloudWatch Events:

```Amazon EC2 instances
AWS Lambda functions
Streams in Amazon Kinesis Streams
Delivery streams in Amazon Kinesis Firehose
Amazon ECS tasks
SSM Run Command
SSM Automation
Step Functions state machines
Pipelines in AWS CodePipeline
Amazon Inspector assessment templates
Amazon SNS topics
Amazon SQS queues
Built-in targets
The default event bus of another AWS account
```

That's a lot more than just Lambda, so I'm not sure why you state in your question that Lambda is the only option. The options of Amazon EC2 instances and Amazon SNS topics both provide a method for Amazon to "push" the events to your services, instead of requiring your services to poll.

Body:
From the documentation:

You can configure the following AWS services as targets for CloudWatch Events:

Amazon EC2 instances
AWS Lambda functions
Streams in Amazon Kinesis Streams
Delivery streams in Amazon Kinesis Firehose
Amazon ECS tasks
SSM Run Command
SSM Automation
Step Functions state machines
Pipelines in AWS CodePipeline
Amazon Inspector assessment templates
Amazon SNS topics
Amazon SQS queues
Built-in targets
The default event bus of another AWS account

That's a lot more than just Lambda, so I'm not sure why you state in your question that Lambda is the only option. The options of Amazon EC2 instances and Amazon SNS topics both provide a method for Amazon to "push" the events to your services, instead of requiring your services to poll.


问:
Isolation level required for reliable de/increments on a single field
Imagine we have a table as follows,

```<code>+----+---------+--------+
| id | Name    | Bunnies|
+----+---------+--------+
|  1 | England |   1000 |
|  2 | Russia  |   1000 |
+----+---------+--------+
</code>```

And we have multiple users removing bunnies, for a specified period, such as 2 hours. (So minimum 0 bunnies, max 1000 bunnies, bunnies are returned, not added by users)

I'm using two basic transaction queries like

```<code>BEGIN;
  UPDATE `BunnyTracker` SET `Bunnies`=`Bunnies`+1 where `id`=1;
COMMIT;
</code>```

When someone returns a bunny and,

```<code>BEGIN;
  UPDATE `BunnyTracker` SET `Bunnies`=`Bunnies`-1 where `id`=1 AND `Bunnies` > 0;
COMMIT;
</code>```

When someone attempts to take a bunny. I'm assuming those queries will implement some sort of atomicity under the hood

It's imperative that users cannot take more bunnies than each country has, (ie. -23 bunnies if 23 users transact concurrently)

My issue is, how do I maintain ACID safety in this case, while being able to concurrently add/increment/decrement the bunnies field, while staying within the bounds (0-1000)
I could set the isolation level to serialized, but I'm worried that would kill performance.

Any tips?
Thanks in advance

答:
Although it looks to the users like multiple transactions occur simultaneously within the DB they are actually sequential.  (E.g. entries get written to the redo/transaction logs one at a time).

Would it therefore work for you to put a constraint on the table "bunnies >= 0" and catch the failure of a transaction which attempts to breach that constraint?

Body:
Imagine we have a table as follows,

+----+---------+--------+
| id | Name    | Bunnies|
+----+---------+--------+
|  1 | England |   1000 |
|  2 | Russia  |   1000 |
+----+---------+--------+

And we have multiple users removing bunnies, for a specified period, such as 2 hours. (So minimum 0 bunnies, max 1000 bunnies, bunnies are returned, not added by users)

I'm using two basic transaction queries like

BEGIN;
  UPDATE `BunnyTracker` SET `Bunnies`=`Bunnies`+1 where `id`=1;
COMMIT;

When someone returns a bunny and,

BEGIN;
  UPDATE `BunnyTracker` SET `Bunnies`=`Bunnies`-1 where `id`=1 AND `Bunnies` > 0;
COMMIT;

When someone attempts to take a bunny. I'm assuming those queries will implement some sort of atomicity under the hood

It's imperative that users cannot take more bunnies than each country has, (ie. -23 bunnies if 23 users transact concurrently)

My issue is, how do I maintain ACID safety in this case, while being able to concurrently add/increment/decrement the bunnies field, while staying within the bounds (0-1000)
I could set the isolation level to serialized, but I'm worried that would kill performance.

Any tips?
Thanks in advance


问:
how to automatically delete a record from a structure after a specific time in c++
i have a structure containing data which are stored in a linked list along with its time of entry into the list. I want the data to get expired after a specific time (say 2 minutes). How to implement this in c++?</p>

答:
Since you didn't provide any code for us to review, here is the general idea for a method to remove the expired data from your linked list.</p>

```<code>#include <ctime>

static double const TIME_EXPIRED = 120.0; // seconds

void removeExpired()
{
    while ( ! myList.empty() )
    {
        double secondsElapsed = difftime(time(), myStructInList.entryTime);
        if (secondsElapsed > TIME_EXPIRED)
        {
            // remove element
        }
    }
}
</code>```

Body:
Since you didn't provide any code for us to review, here is the general idea for a method to remove the expired data from your linked list.

#include <ctime>

static double const TIME_EXPIRED = 120.0; // seconds

void removeExpired()
{
    while ( ! myList.empty() )
    {
        double secondsElapsed = difftime(time(), myStructInList.entryTime);
        if (secondsElapsed > TIME_EXPIRED)
        {
            // remove element
        }
    }
}