问:
Best performance in filtering large datasets: = ANY() or @> on an array column; XOR on a bitmapped binary column; join a m2m table or something else?
The General Question:

This question pertains to tables with a large number of rows (say millions) that has a many to many relationship with a relatively small set of data (say tens).  For example you might have 20 different tags or types or categories and each of your 10 million records is associated with one or more of them.

You could have a separate table for your tags with 20 rows and then use a many to many table to define the relationships between those 20 tags and your 10 million records.

In Postgres there are array types that could be used instead. In mySQL there is a the SET column type that like that.

You could also use a bitmap or bit string type and perform an XOR on that column.

My question is which of these solutions (or an alternative solution) is best for performance when querying the large table for records that are associated with one member of the small set.  A solution should include what indexes to create and use.

My Specifics:

I've tried to keep the question here as generic as possible because I believe the answer could be applicable in many fairly common scenarios.  However, for clarity I'll describe my specific situation now.

I have a table with millions of titles.  Each title is associated with one or more languages.  For example 'Don Quixote' may be 'Дон Кихот' in Russian, Bulgarian, Kazakh and 'Don Quijote' in a bunch of other languages.  I have a search string and a language and want to find the best match in titles.  I'm using Postgres and have a trigram index using gin on the titles.  An example search would be find matches for 'Дон Кихот' in Bulgarian.

Currently have have the languages in a char(2)[] array type column using two letter language codes.  I assume using an int array with language IDs would be better, but I want to go for what is best.  I'm not worried about how much effort it would take to setup a bitmap for languages to do an XOR search or whatever effort and complexity is involved in implementing a particular solution.  The performance is what matters.

I would tend to think that JOINing a many to many table would not be the best solution because that table would have multiple entries per row in the title table and so it would be huge.  But perhaps I'm wrong about that because that is what RDBMS are designed to do.

Huge thanks to all of you who spend your highly valuable time answering these questions.

答:
The postgreSQL documentation warns against searching arrays.

Arrays are not sets; searching for specific array elements can be a sign of database misdesign. Consider using a separate table with a row for each item that would be an array element. This will be easier to search, and is likely to scale better for a large number of elements.

SQL in general, and postgreSQL in particular, is astonishingly good at joining vast many-to-many tables, especially when their rows are modestly sized, their columns' data types match the columns you use for joining, and they have the right indexes.

So avoid arrays for the application you describe.

For a two-column junction table like you describe, you'd define it as something like this:

CREATE TABLE title_lang (
    title_id BIGINT NOT NULL,
    lang_id SMALLINT NOT NULL,
    PRIMARY KEY (title_id, lang_id)
};

CREATE INDEX lang_title ON title_lang (lang_id, title_id);
```

I used SMALLINT for the language id. When you use character types, the database gives you unicode characters. Those take unexpected amounts of space. And indexes on integers are very efficient. But you should use the data type that makes sense in the rest of your schema.

I suggest a primary key going from title to language, and a reverse key going the other way. You can omit the reverse key if you don't go from language to title.

To associate a title with a language you insert a row in the table. To get rid of the association you delete the row.

Body:
The postgreSQL documentation warns against searching arrays.

Arrays are not sets; searching for specific array elements can be a sign of database misdesign. Consider using a separate table with a row for each item that would be an array element. This will be easier to search, and is likely to scale better for a large number of elements.

SQL in general, and postgreSQL in particular, is astonishingly good at joining vast many-to-many tables, especially when their rows are modestly sized, their columns' data types match the columns you use for joining, and they have the right indexes.

So avoid arrays for the application you describe.

For a two-column junction table like you describe, you'd define it as something like this:

CREATE TABLE title_lang (
    title_id BIGINT NOT NULL,
    lang_id SMALLINT NOT NULL,
    PRIMARY KEY (title_id, lang_id)
};

CREATE INDEX lang_title ON title_lang (lang_id, title_id);

I used SMALLINT for the language id. When you use character types, the database gives you unicode characters. Those take unexpected amounts of space. And indexes on integers are very efficient. But you should use the data type that makes sense in the rest of your schema.

I suggest a primary key going from title to language, and a reverse key going the other way. You can omit the reverse key if you don't go from language to title.

To associate a title with a language you insert a row in the table. To get rid of the association you delete the row.


来源: stackexchange

问:
Java get duplicated elements more than 2 list
I have 9 list and I want to compare all of them and get duplicated elements.

I tried  retainAll() method but it deletes elements that is not duplicated.

For example when I only compare SUPPORT and PROJECT_AND_SUPPORT_NAMES I get duplicated value.

But when I compare like below it returns empty.

```<code>        MANAGEMENT_NAMES.retainAll(SUPPORT_NAMES);
        MANAGEMENT_NAMES.retainAll(PROJECT_AND_SUPPORT_NAMES);
        MANAGEMENT_NAMES.retainAll(SALES_NAMES);
        MANAGEMENT_NAMES.retainAll(MARKETING_NAMES);
        MANAGEMENT_NAMES.retainAll(ACADEMY_NAMES);
        MANAGEMENT_NAMES.retainAll(DEVELOPMENT_NAMES);
        MANAGEMENT_NAMES.retainAll(HR_AND_ADMINISTRATION_NAMES);
        MANAGEMENT_NAMES.retainAll(WAREHOUSE_NAMES);

        SUPPORT_NAMES.retainAll(PROJECT_AND_SUPPORT_NAMES);
        SUPPORT_NAMES.retainAll(SALES_NAMES);
        SUPPORT_NAMES.retainAll(MARKETING_NAMES);
        SUPPORT_NAMES.retainAll(ACADEMY_NAMES);
        SUPPORT_NAMES.retainAll(DEVELOPMENT_NAMES);
        SUPPORT_NAMES.retainAll(HR_AND_ADMINISTRATION_NAMES);
        SUPPORT_NAMES.retainAll(WAREHOUSE_NAMES);

        PROJECT_AND_SUPPORT_NAMES.retainAll(SALES_NAMES);
        PROJECT_AND_SUPPORT_NAMES.retainAll(MARKETING_NAMES);
        PROJECT_AND_SUPPORT_NAMES.retainAll(ACADEMY_NAMES);
        PROJECT_AND_SUPPORT_NAMES.retainAll(DEVELOPMENT_NAMES);
        PROJECT_AND_SUPPORT_NAMES.retainAll(HR_AND_ADMINISTRATION_NAMES);
        PROJECT_AND_SUPPORT_NAMES.retainAll(WAREHOUSE_NAMES);
.
.
.

        HR_AND_ADMINISTRATION_NAMES.retainAll(WAREHOUSE_NAMES);
</code>```

I want to get result like :

Department A - Jerry

Department B - Chris

Department C - Jerry

Department D - Chris

Department E - Chris

Department F - Jerry

答:
If I understand you correctly duplicate is a name that appears in all the departments more than once. And as a result, you want to have duplicated names for each department separately.

I want to get result like :

Department A - Jerry
Department B - Chris

So my idea is first to create a Set of all duplicates in all the departments. And then create lists of duplicates for each department separately based on the Set.

This solution has linear time complexity.

Method getDuplicatesForAllDepartments() iterates over all the names in all departments and counts the number of occurrences of each name. It retains only names that appear more than once and saves them to a Set.

Method getDuplicatesForOneDepartments() determines which names in the given department are contained in the Set of duplicates.

```<code>    public static void main(String[] args) {
        List<List<String>> departments = List.of(SUPPORT_NAMES, PROJECT_AND_SUPPORT_NAMES, SALES_NAMES, ... etc);
//         * List.of() - works with Java 9 onwards
//        For Java 8 you can add departments one by one using Collection.addAll()
//        List<List<String>> departments = new ArrayList<>();
//        Collections.addAll(departments, SUPPORT_NAMES, PROJECT_AND_SUPPORT_NAMES, SALES_NAMES, ... etc);

        Set<String> allDuplicates = getDuplicatesForAllDepartments(departments);
        List<String> duplicates1 = getDuplicatesForOneDepartment(SUPPORT_NAMES, allDuplicates);
        List<String> duplicatesProjectAndSupport = getDuplicatesForOneDepartment(PROJECT_AND_SUPPORT_NAMES, allDuplicates);
        // ... etc
    }

    public static Set<String> getDuplicatesForAllDepartments(List<List<String>> departments) {
        return departments.stream()
                .flatMap(List::stream)
                .collect(Collectors.groupingBy(UnaryOperator.identity(),
                                               Collectors.counting()))
                .entrySet().stream()
                .filter(entry -> entry.getValue() > 1)
                .map(Map.Entry::getKey)
                .collect(Collectors.toSet());
    }

    public static List<String> getDuplicatesForOneDepartment(List<String> department, Set<String> allDuplicates) {
        return department.stream()
                .filter(allDuplicates::contains)
                .collect(Collectors.toList());
    }
</code>```

The imperative implementations of methods getDuplicatesForAllDepartments() and getDuplicatesForOneDepartment() will look like this:

```<code>    public static Set<String> getDuplicatesForAllDepartments(List<List<String>> departments) {
        Map<String, Integer> nameToCount = new HashMap<>();
        for (List<String> department: departments) {
            for (String name: department) {
                nameToCount.merge(name, 1, Integer::sum);
            }
        }

        Set<String> duplicates = new HashSet<>();
        for (Map.Entry<String, Integer> entry: nameToCount.entrySet()) {
            if (entry.getValue() > 1) {
                duplicates.add(entry.getKey());
            }
        }
        return duplicates;
    }

    public static List<String> getDuplicatesForOneDepartment(List<String> department, Set<String> allDuplicates) {
        List<String> duplicates = new ArrayList<>();
        for (String name: department) {
            if (allDuplicates.contains((name))) {
                duplicates.add(name);
            }
        }
        return duplicates;
    }
</code>```

I've used your example to prove that both implementations a working.

```<code>        List<String> departmentA = List.of("Jerry", "Stephen", "Daniel");
        // for Java 8 Arrays.asList("Jerry", "Stephen", "Daniel");
        List<String> departmentB = List.of("Chris", "Earl", "Ryan");
        List<String> departmentC = List.of("Jerry", "Brown", "Micheal");

        List<List<String>> departments = new ArrayList<>();
        Collections.addAll(departments, departmentA, departmentB, departmentC);
        System.out.println(getDuplicatesForAllDepartments(departments));
</code>```

output

```<code>[Jerry]
</code>```

ContentLicense: CC BY-SA 4.0

Body:
I have 9 list and I want to compare all of them and get duplicated elements.

I tried  retainAll() method but it deletes elements that is not duplicated.

For example when I only compare SUPPORT and PROJECT_AND_SUPPORT_NAMES I get duplicated value.

But when I compare like below it returns empty.

        MANAGEMENT_NAMES.retainAll(SUPPORT_NAMES);
        MANAGEMENT_NAMES.retainAll(PROJECT_AND_SUPPORT_NAMES);
        MANAGEMENT_NAMES.retainAll(SALES_NAMES);
        MANAGEMENT_NAMES.retainAll(MARKETING_NAMES);
        MANAGEMENT_NAMES.retainAll(ACADEMY_NAMES);
        MANAGEMENT_NAMES.retainAll(DEVELOPMENT_NAMES);
        MANAGEMENT_NAMES.retainAll(HR_AND_ADMINISTRATION_NAMES);
        MANAGEMENT_NAMES.retainAll(WAREHOUSE_NAMES);

        SUPPORT_NAMES.retainAll(PROJECT_AND_SUPPORT_NAMES);
        SUPPORT_NAMES.retainAll(SALES_NAMES);
        SUPPORT_NAMES.retainAll(MARKETING_NAMES);
        SUPPORT_NAMES.retainAll(ACADEMY_NAMES);
        SUPPORT_NAMES.retainAll(DEVELOPMENT_NAMES);
        SUPPORT_NAMES.retainAll(HR_AND_ADMINISTRATION_NAMES);
        SUPPORT_NAMES.retainAll(WAREHOUSE_NAMES);

        PROJECT_AND_SUPPORT_NAMES.retainAll(SALES_NAMES);
        PROJECT_AND_SUPPORT_NAMES.retainAll(MARKETING_NAMES);
        PROJECT_AND_SUPPORT_NAMES.retainAll(ACADEMY_NAMES);
        PROJECT_AND_SUPPORT_NAMES.retainAll(DEVELOPMENT_NAMES);
        PROJECT_AND_SUPPORT_NAMES.retainAll(HR_AND_ADMINISTRATION_NAMES);
        PROJECT_AND_SUPPORT_NAMES.retainAll(WAREHOUSE_NAMES);
.
.
.

        HR_AND_ADMINISTRATION_NAMES.retainAll(WAREHOUSE_NAMES);

I want to get result like :

Department A - Jerry

Department B - Chris

Department C - Jerry

Department D - Chris

Department E - Chris

Department F - Jerry

Body:
If I understand you correctly duplicate is a name that appears in all the departments more than once. And as a result, you want to have duplicated names for each department separately.

I want to get result like :

Department A - Jerry
Department B - Chris

So my idea is first to create a Set of all duplicates in all the departments. And then create lists of duplicates for each department separately based on the Set.

This solution has linear time complexity.

Method getDuplicatesForAllDepartments() iterates over all the names in all departments and counts the number of occurrences of each name. It retains only names that appear more than once and saves them to a Set.

Method getDuplicatesForOneDepartments() determines which names in the given department are contained in the Set of duplicates.

    public static void main(String[] args) {
        List<List<String>> departments = List.of(SUPPORT_NAMES, PROJECT_AND_SUPPORT_NAMES, SALES_NAMES, ... etc);
//         * List.of() - works with Java 9 onwards
//        For Java 8 you can add departments one by one using Collection.addAll()
//        List<List<String>> departments = new ArrayList<>();
//        Collections.addAll(departments, SUPPORT_NAMES, PROJECT_AND_SUPPORT_NAMES, SALES_NAMES, ... etc);

        Set<String> allDuplicates = getDuplicatesForAllDepartments(departments);
        List<String> duplicates1 = getDuplicatesForOneDepartment(SUPPORT_NAMES, allDuplicates);
        List<String> duplicatesProjectAndSupport = getDuplicatesForOneDepartment(PROJECT_AND_SUPPORT_NAMES, allDuplicates);
        // ... etc
    }

    public static Set<String> getDuplicatesForAllDepartments(List<List<String>> departments) {
        return departments.stream()
                .flatMap(List::stream)
                .collect(Collectors.groupingBy(UnaryOperator.identity(),
                                               Collectors.counting()))
                .entrySet().stream()
                .filter(entry -> entry.getValue() > 1)
                .map(Map.Entry::getKey)
                .collect(Collectors.toSet());
    }

    public static List<String> getDuplicatesForOneDepartment(List<String> department, Set<String> allDuplicates) {
        return department.stream()
                .filter(allDuplicates::contains)
                .collect(Collectors.toList());
    }

The imperative implementations of methods getDuplicatesForAllDepartments() and getDuplicatesForOneDepartment() will look like this:

    public static Set<String> getDuplicatesForAllDepartments(List<List<String>> departments) {
        Map<String, Integer> nameToCount = new HashMap<>();
        for (List<String> department: departments) {
            for (String name: department) {
                nameToCount.merge(name, 1, Integer::sum);
            }
        }

        Set<String> duplicates = new HashSet<>();
        for (Map.Entry<String, Integer> entry: nameToCount.entrySet()) {
            if (entry.getValue() > 1) {
                duplicates.add(entry.getKey());
            }
        }
        return duplicates;
    }

    public static List<String> getDuplicatesForOneDepartment(List<String> department, Set<String> allDuplicates) {
        List<String> duplicates = new ArrayList<>();
        for (String name: department) {
            if (allDuplicates.contains((name))) {
                duplicates.add(name);
            }
        }
        return duplicates;
    }

I've used your example to prove that both implementations a working.

        List<String> departmentA = List.of("Jerry", "Stephen", "Daniel");
        // for Java 8 Arrays.asList("Jerry", "Stephen", "Daniel");
        List<String> departmentB = List.of("Chris", "Earl", "Ryan");
        List<String> departmentC = List.of("Jerry", "Brown", "Micheal");

        List<List<String>> departments = new ArrayList<>();
        Collections.addAll(departments, departmentA, departmentB, departmentC);
        System.out.println(getDuplicatesForAllDepartments(departments));

output

[Jerry]


问:
help with event click
I'm developing a html with this structure

```<code>div id="menuContainer">
    <div class = "menu ui-accordion-header ui-state-default ui-corner-all">
        <label class="formatText">Cliente</label><span class="ui-icon ui-icon-triangle-1-e menuIcon" style="float: right"></span>
            <div class = "subMenu ui-accordion-header ui-state-default ui-corner-all">
                <ul class="options">
                    <li>
                        <label class="formatText">Ver Cliente</label><span class="ui-icon ui-icon-triangle-1-e" style="float: right"></span>
                            <div class = "subMenuRight ui-accordion-header ui-state-default ui-corner-all">
                                <ul class="options">
                                    <li>
                                        <label class="formatText">Por Nombre</label><span class="ui-icon ui-icon-triangle-1-e" style="float: right"></span>
                                            <div class = "subMenuRight ui-accordion-header ui-state-default ui-corner-all">
                                                <ul class="options">
                                                    <li><label class="formatText">sub sub menu</label></li>
                                                    <li><label class="formatText">prueba</label></li>
                                                </ul>
                                            </div>
                                    </li>
                                    <li>
                                        <label class="formatText">Por Campana</label>
                                    </li>
                                </ul>
                            </div>
                    </li>
                    <li>
                        <label class="formatText">Reportes para Cliente</label>
                    </li>
                </ul>
            </div>
    </div>
</div>
</code>```

notice that the principal div have class called menu, in the inside there are a some divs that with a classes called subMenu and subMenuRight, my problem it's that i want associate a event click to the father and other behavior to the divs children i tried like this

```<code>$(".menu").click(function () {
    alert('click in the father');
});
$(".subMenu li").click(function () {
    alert('click in the first child');
});
$(".subMenuRight li").click(function () {
    alert('click in the second child');
});
</code>```

The problem with this code it's that div father contains the divs children all the event handler are triggered. So my question is How can I modify the selector or validate the in the event handler for to only fire the exactly element that is clicked??

答:
If you want to stop event bubling just return false from the handler.</p>

```<code>$(".menu").click(function () {
    alert('click in the father');
    return false;
});
$(".subMenu li").click(function () {
    alert('click in the first child');
    return false;
});
$(".subMenuRight li").click(function () {
    alert('click in the second child');
    return false;
});
</code>```

ContentLicense: CC BY-SA 3.0

Title: help with event click

Body:
I'm developing a html with this structure

div id="menuContainer">

        Cliente

                        Ver Cliente

                                        Por Nombre

sub sub menu

prueba

                                        Por Campana

                        Reportes para Cliente

notice that the principal div have class called menu, in the inside there are a some divs that with a classes called subMenu and subMenuRight, my problem it's that i want associate a event click to the father and other behavior to the divs children i tried like this

$(".menu").click(function () {
    alert('click in the father');
});
$(".subMenu li").click(function () {
    alert('click in the first child');
});
$(".subMenuRight li").click(function () {
    alert('click in the second child');
});

The problem with this code it's that div father contains the divs children all the event handler are triggered. So my question is How can I modify the selector or validate the in the event handler for to only fire the exactly element that is clicked??

Body:
If you want to stop event bubling just return false from the handler.

$(".menu").click(function () {
    alert('click in the father');
    return false;
});
$(".subMenu li").click(function () {
    alert('click in the first child');
    return false;
});
$(".subMenuRight li").click(function () {
    alert('click in the second child');
    return false;
});


问:
How to use Inner Joins with where clause?
I am here posting some extension of How to concatenate more sql queries to get one table? question to get more specific query results.

Let assume we are having now this type of dataset.

```<code>query1 : select * from userdetails1.UserTable;

Result1:
UserID  Username    Status
1         John      Active
2         Micheal   Active
3         Ann       Active
4         Shasha    Resigned
5         mike      Resigned

query2 :
select *
from userdetails2.AmountTable
where TotalAmountResetDate ='2022-01-31' ;

result2:
UserId  TotalAmount TotalAmountResetDate
1          -12         1/31/2022
2          345         1/31/2022
3          213         1/31/2022
4          0           1/31/2022
5          0           1/31/2022

query3:
select Username,sum(TotalInvested) as TotalInvested
from userdetails2.TotalInvestmentsTable
where total_invested_date between '2022-01-31' and '2022-02-12'
group by Username;

result3:

Username    Totalnvested
John         45000
Micheal      45000
Ann          45000

query4:
select Username,sum(TotalExpenses)
from userdetails2.TotalExpensesTable
where date_of_expense_date between '2022-01-31' and '2022-02-12'
group by Username;

Username    TotalExpenses
John           2543
Micheal        2345
Ann            3290
</code>```

Then how I get the output as below,

```<code>UserId  Username    TotalAmountResetDate    TotalAmount Totalnvested    TotalExpenses   TotalBalance
1        John              1/31/2022               -12       45000            2543          42445
2        Micheal           1/31/2022               345       45000            2345          43000
3        Ann               1/31/2022               213       45000            3290          41923
4        Shasha            1/31/2022               0         0                 0            0
5        mike              1/31/2022               0         0                 0            0
</code>```

From the answers I have made this answer, but seems it is not getting what I need, it removes Zero values. Can someone help?

```<code>    SELECT t2.UserID,
       t1.Username,
       t2.TotalAmountResetDate,
       t2.TotalAmount,
       COALESCE(Totalnvested,0) Totalnvested,
       COALESCE(TotalExpenses,0) TotalExpenses,
       t2.TotalAmount + COALESCE(Totalnvested,0) - COALESCE(TotalExpenses,0) TotalBalance
FROM userdetails1.UserTable t1
INNER JOIN userdetails2.AmountTable t2
ON t1.UserID = t2.UserID
LEFT JOIN userdetails2.TotalInvestmentsTable investment
ON t1.Username = investment.Username
LEFT JOIN userdetails2.TotalExpensesTable expenses
ON t1.Username = expenses.Username
where t2.TotalAmountResetDate='2022-01-31' and investment.total_invested_date between '2022-01-31' and '2022-02-12';
</code>```

答:
You might want to let the condition move to ON from WHERE because you are doing OUTER JOIN

```<code>SELECT t2.UserID,
       t1.Username,
       t2.TotalAmountResetDate,
       t2.TotalAmount,
       COALESCE(Totalnvested,0) Totalnvested,
       COALESCE(TotalExpenses,0) TotalExpenses,
       t2.TotalAmount + COALESCE(Totalnvested,0) - COALESCE(TotalExpenses,0) TotalBalance
FROM userdetails1.UserTable t1
INNER JOIN userdetails2.AmountTable t2
ON t1.UserID = t2.UserID
LEFT JOIN userdetails2.TotalInvestmentsTable investment
ON t1.Username = investment.Username
LEFT JOIN userdetails2.TotalExpensesTable expenses
ON t1.Username = expenses.Username AND
investment.total_invested_date BETWEEN '2022-01-31' and '2022-02-12'
WHERE t2.TotalAmountResetDate='2022-01-31';
</code>```

Body:
I am here posting some extension of How to concatenate more sql queries to get one table? question to get more specific query results.

Let assume we are having now this type of dataset.

query1 : select * from userdetails1.UserTable;

Result1:
UserID  Username    Status
1         John      Active
2         Micheal   Active
3         Ann       Active
4         Shasha    Resigned
5         mike      Resigned

query2 :
select *
from userdetails2.AmountTable
where TotalAmountResetDate ='2022-01-31' ;

result2:
UserId  TotalAmount TotalAmountResetDate
1          -12         1/31/2022
2          345         1/31/2022
3          213         1/31/2022
4          0           1/31/2022
5          0           1/31/2022

query3:
select Username,sum(TotalInvested) as TotalInvested
from userdetails2.TotalInvestmentsTable
where total_invested_date between '2022-01-31' and '2022-02-12'
group by Username;

result3:

Username    Totalnvested
John         45000
Micheal      45000
Ann          45000

query4:
select Username,sum(TotalExpenses)
from userdetails2.TotalExpensesTable
where date_of_expense_date between '2022-01-31' and '2022-02-12'
group by Username;

Username    TotalExpenses
John           2543
Micheal        2345
Ann            3290

Then how I get the output as below,

UserId  Username    TotalAmountResetDate    TotalAmount Totalnvested    TotalExpenses   TotalBalance
1        John              1/31/2022               -12       45000            2543          42445
2        Micheal           1/31/2022               345       45000            2345          43000
3        Ann               1/31/2022               213       45000            3290          41923
4        Shasha            1/31/2022               0         0                 0            0
5        mike              1/31/2022               0         0                 0            0

From the answers I have made this answer, but seems it is not getting what I need, it removes Zero values. Can someone help?

    SELECT t2.UserID,
       t1.Username,
       t2.TotalAmountResetDate,
       t2.TotalAmount,
       COALESCE(Totalnvested,0) Totalnvested,
       COALESCE(TotalExpenses,0) TotalExpenses,
       t2.TotalAmount + COALESCE(Totalnvested,0) - COALESCE(TotalExpenses,0) TotalBalance
FROM userdetails1.UserTable t1
INNER JOIN userdetails2.AmountTable t2
ON t1.UserID = t2.UserID
LEFT JOIN userdetails2.TotalInvestmentsTable investment
ON t1.Username = investment.Username
LEFT JOIN userdetails2.TotalExpensesTable expenses
ON t1.Username = expenses.Username
where t2.TotalAmountResetDate='2022-01-31' and investment.total_invested_date between '2022-01-31' and '2022-02-12';

Body:
You might want to let the condition move to ON from WHERE because you are doing OUTER JOIN

SELECT t2.UserID,
       t1.Username,
       t2.TotalAmountResetDate,
       t2.TotalAmount,
       COALESCE(Totalnvested,0) Totalnvested,
       COALESCE(TotalExpenses,0) TotalExpenses,
       t2.TotalAmount + COALESCE(Totalnvested,0) - COALESCE(TotalExpenses,0) TotalBalance
FROM userdetails1.UserTable t1
INNER JOIN userdetails2.AmountTable t2
ON t1.UserID = t2.UserID
LEFT JOIN userdetails2.TotalInvestmentsTable investment
ON t1.Username = investment.Username
LEFT JOIN userdetails2.TotalExpensesTable expenses
ON t1.Username = expenses.Username AND
investment.total_invested_date BETWEEN '2022-01-31' and '2022-02-12'
WHERE t2.TotalAmountResetDate='2022-01-31';


问:
Flask validate_on_submit always False
I know that there are similar problems which have been answered. The csrf_enabled is not an issue now if the Form inheriting FlaskForm,  and the template has the form.hidden_tag().

I have the following flask app.

```<code>## Filenname: app.py

from flask import Flask, render_template, redirect, url_for, flash, request
from flask_wtf import FlaskForm
from wtforms import StringField, SubmitField, SelectField
from wtforms.validators import DataRequired

app = Flask(__name__)

app.config["SECRET_KEY"] = "secret"

class DataForm(FlaskForm):
    name = StringField("Name", validators=[DataRequired()])
    gender = SelectField("Gender", validators=None, choices=[(1, 'M'), (2, "F")])
    submit = SubmitField("Submit", validators=None)

@app.route('/index', methods=["GET", "POST"])
def index():
    form = DataForm(request.form)
    print(form.validate_on_submit())
    if form.validate_on_submit():
        print(form.validate())
        print(form.name)
        flash("THIS IS FLASH")
        title="hello"
        return redirect(url_for('output'))
    return render_template('index.html', form=form)

@app.route('/output', methods=["GET", "POST"])
def output():
    title = "hello"
    form = DataForm()
    print(form.validate())
    return render_template('output.html', title=title)

app.run(debug=False)
</code>```

The following is index.html template:

```<code><html>
    <body>
        {% with messages = get_flashed_messages() %}
        {{ messages }}
        {% endwith %}

        <form action="" method="GET">
            {{ form.hidden_tag() }}
            {{ form.name.label }}
            {{ form.name() }}
            {% for error in form.name.errors %}
            <span style="color: red;">[{{ error }}]</span>
            {% endfor %}

            <hr>

            {{ form.gender.label }}
            {{ form.gender() }}

            {{ form.submit() }}
        </form>
    </body>
</html>
</code>```

After clicking the submit button the execution never goes in the if form.validate_on_submit() block in the index function.

I also removed all the validators, the code inside validate_on_submit block is still unreachable. Printing form.validate_on_submit() is always false.

答:
just make form without

2.form = FlaskForm(meta={'csrf': False})

Title: Flask validate_on_submit always False

Body:
I know that there are similar problems which have been answered. The csrf_enabled is not an issue now if the Form inheriting FlaskForm,  and the template has the form.hidden_tag().

I have the following flask app.

## Filenname: app.py

from flask import Flask, render_template, redirect, url_for, flash, request
from flask_wtf import FlaskForm
from wtforms import StringField, SubmitField, SelectField
from wtforms.validators import DataRequired

app = Flask(__name__)

app.config["SECRET_KEY"] = "secret"

class DataForm(FlaskForm):
    name = StringField("Name", validators=[DataRequired()])
    gender = SelectField("Gender", validators=None, choices=[(1, 'M'), (2, "F")])
    submit = SubmitField("Submit", validators=None)

@app.route('/index', methods=["GET", "POST"])
def index():
    form = DataForm(request.form)
    print(form.validate_on_submit())
    if form.validate_on_submit():
        print(form.validate())
        print(form.name)
        flash("THIS IS FLASH")
        title="hello"
        return redirect(url_for('output'))
    return render_template('index.html', form=form)

@app.route('/output', methods=["GET", "POST"])
def output():
    title = "hello"
    form = DataForm()
    print(form.validate())
    return render_template('output.html', title=title)

app.run(debug=False)

The following is index.html template:

        {% with messages = get_flashed_messages() %}
        {{ messages }}
        {% endwith %}

        <form action="" method="GET">
            {{ form.hidden_tag() }}
            {{ form.name.label }}
            {{ form.name() }}
            {% for error in form.name.errors %}
            [{{ error }}]
            {% endfor %}

            {{ form.gender.label }}
            {{ form.gender() }}

            {{ form.submit() }}
        </form>

After clicking the submit button the execution never goes in the if form.validate_on_submit() block in the index function.

I also removed all the validators, the code inside validate_on_submit block is still unreachable. Printing form.validate_on_submit() is always false.


答:
Simply outer join the queries to your user table.

```<code>select
  u.username,
  a.totalamount,
  a.totalamountresetdate,
  coalesce(i.total, 0) as totalinvested,
  coalesce(e.total, 0) as totalexpenses,
  a.totalamount + coalesce(i.total, 0) - coalesce(e.total, 0) as totalbalance
from userdetails1.usertable u
left join userdetails2.amounttable a
            on a.userid = u.userid
           and a.totalamountresetdate = date '2022-01-31'
left join
(
  select username, sum(totalinvested) as total
  from userdetails2.totalinvestmentstable
  where total_invested_date between date '2022-01-31' and date '2022-02-12'
  group by username
) i on i.username = u.username
left join
(
  select username, sum(totalexpenses) as total
  from userdetails2.totalexpensestable
  where date_of_expense_date between date '2022-01-31' and date '2022-02-12'
  group by username
) e on e.username = u.username
order by u.username;
</code>```

(It seems a bad idea by the way to use the username instead of the userid for the investment and expense tables. What do you have the userid for then?)

Title: How to use Inner Joins with where clause?

Body:
Simply outer join the queries to your user table.

select
  u.username,
  a.totalamount,
  a.totalamountresetdate,
  coalesce(i.total, 0) as totalinvested,
  coalesce(e.total, 0) as totalexpenses,
  a.totalamount + coalesce(i.total, 0) - coalesce(e.total, 0) as totalbalance
from userdetails1.usertable u
left join userdetails2.amounttable a
            on a.userid = u.userid
           and a.totalamountresetdate = date '2022-01-31'
left join
(
  select username, sum(totalinvested) as total
  from userdetails2.totalinvestmentstable
  where total_invested_date between date '2022-01-31' and date '2022-02-12'
  group by username
) i on i.username = u.username
left join
(
  select username, sum(totalexpenses) as total
  from userdetails2.totalexpensestable
  where date_of_expense_date between date '2022-01-31' and date '2022-02-12'
  group by username
) e on e.username = u.username
order by u.username;

(It seems a bad idea by the way to use the username instead of the userid for the investment and expense tables. What do you have the userid for then?)


问:
Data refresh issue with D3 burnup chart
I'm building a "burnup" d3 line chart attempting to forecast trends based on scope and historic data.

A dropdown box selects the data to be displayed, ideally transitioning between lines, but I'm having troubles clearing the previous data displayed, and instead the new lines are written over the existing lines.

Link to the jsfiddle: https://jsfiddle.net/dgf1vts8/

Currently doing it this way (line 329):

```<code>svg.append("path")
      .datum(selectedData)
      .attr("class", "line")
      .attr("d", line);
</code>```

other approach I've tried and did not work (line 318)

```<code>var lines = svg.selectAll(".line").data(selectedData).attr("class","line");
    lines.transition().duration(500).attr("d",line);
    lines.enter()
      .append("path")
      .attr("class","line")
      .attr("d",line);
    lines.exit()
      .remove();
</code>```

Any guidance with this would be much appreciated

答:
Since you only append new object in your update function, every time that function is invoked a new line is added to the chart.

The easiest workaround would be to just remove all path objects before you add the new ones.

```<code>svg.selectAll("path").remove();
</code>```

However, the enter-update-exit logic for this would be

```<code>  var lines = svg.selectAll(".line").data(selectedGroup);
  lines.enter() // enter any new data
    .append("path")
    .attr("class", "line")
    .merge(lines)
    .datum(selectedData)
    .attr("d", line);
  lines.exit() // exit
    .remove();
</code>```

The logic for lines is different as for e. g. points or bars, where you need to create an object for every data point of the time series. For lines you only have to create one path object. This is why the data binding to the selection

```<code>svg.selectAll(".line")
</code>```

should be the label of the line. If you work with multiple lines, it is often best to nest the array.

If you start a new project you should probably use the current version d3v7 and not d3v3, since there have been major changes which break compatibility. I reduced your fiddle and changed all the parts to make it work with d3v7: https://jsfiddle.net/esd4kofr/1/

Title: Data refresh issue with D3 burnup chart

Body:
I'm building a "burnup" d3 line chart attempting to forecast trends based on scope and historic data.

A dropdown box selects the data to be displayed, ideally transitioning between lines, but I'm having troubles clearing the previous data displayed, and instead the new lines are written over the existing lines.

Link to the jsfiddle: https://jsfiddle.net/dgf1vts8/

Currently doing it this way (line 329):

svg.append("path")
      .datum(selectedData)
      .attr("class", "line")
      .attr("d", line);

other approach I've tried and did not work (line 318)

var lines = svg.selectAll(".line").data(selectedData).attr("class","line");
    lines.transition().duration(500).attr("d",line);
    lines.enter()
      .append("path")
      .attr("class","line")
      .attr("d",line);
    lines.exit()
      .remove();

Any guidance with this would be much appreciated

Body:
Since you only append new object in your update function, every time that function is invoked a new line is added to the chart.

The easiest workaround would be to just remove all path objects before you add the new ones.

svg.selectAll("path").remove();

However, the enter-update-exit logic for this would be

  var lines = svg.selectAll(".line").data(selectedGroup);
  lines.enter() // enter any new data
    .append("path")
    .attr("class", "line")
    .merge(lines)
    .datum(selectedData)
    .attr("d", line);
  lines.exit() // exit
    .remove();

The logic for lines is different as for e. g. points or bars, where you need to create an object for every data point of the time series. For lines you only have to create one path object. This is why the data binding to the selection

svg.selectAll(".line")

should be the label of the line. If you work with multiple lines, it is often best to nest the array.

If you start a new project you should probably use the current version d3v7 and not d3v3, since there have been major changes which break compatibility. I reduced your fiddle and changed all the parts to make it work with d3v7: https://jsfiddle.net/esd4kofr/1/


问:
REACT onclick change color of div
I am trying to change each div's color onClick. The problem in my code is that all divs change color when one is clicked. Looking for a way to change each one's color individually

Here is my code :

```<code>class Main extends React.PureComponent {
state = {
    color: 'blue',
};
onChange = () => {
    this.setState({color: 'red'});
};
render() {
    return (
        <Box sx={SX.root}>
            <Box sx={SX.page}>
                <Box sx={SX.shapeContainer}>
                    <div
                        style={{backgroundColor: this.state.color, width: 20, height: 20, opacity: '50%'}}
                        onClick={this.onChange}
                    />
                    <div
                        style={{backgroundColor: this.state.color, width: 20, height: 20, opacity: '50%'}}
                        onClick={this.onChange}
                    />
                </Box>
                <MathGrid sx={SX.math1} />
            </Box>

            <Box sx={SX.header} />
            <Box sx={SX.footer} />
        </Box>
    );
}
</code>```

output

答:
You are using the same state for both boxes so when you change your state - its going to change across the whole component. You will need to make separate states for each component. or create a separate component for the box</p>
```<code><div
                        style={{backgroundColor: this.state.color, width: 20, height: 20, opacity: '50%'}}
                        onClick={this.onChange}
                    />
                    <div
                        style={{backgroundColor: this.state.color, width: 20, height: 20, opacity: '50%'}}
                        onClick={this.onChange}
                    />
</code>```

Title: REACT onclick change color of div

Body:
I am trying to change each div's color onClick. The problem in my code is that all divs change color when one is clicked. Looking for a way to change each one's color individually

Here is my code :

class Main extends React.PureComponent {
state = {
    color: 'blue',
};
onChange = () => {
    this.setState({color: 'red'});
};
render() {
    return (
        <Box sx={SX.root}>
            <Box sx={SX.page}>
                <Box sx={SX.shapeContainer}>

                </Box>
                <MathGrid sx={SX.math1} />
            </Box>

            <Box sx={SX.header} />
            <Box sx={SX.footer} />
        </Box>
    );
}

output

Body:
You are using the same state for both boxes so when you change your state - its going to change across the whole component. You will need to make separate states for each component. or create a separate component for the box

<div
                        style={{backgroundColor: this.state.color, width: 20, height: 20, opacity: '50%'}}
                        onClick={this.onChange}
                    />
                    <div
                        style={{backgroundColor: this.state.color, width: 20, height: 20, opacity: '50%'}}
                        onClick={this.onChange}
                    />


问:
Can I input in arrayList similar to array?

public void getDisMarks()
   {
       marks=new int[3];
       System.out.print("Enter marks of Physics: ");
       marks[0]=sc.nextInt();
       System.out.print("Enter marks of Chemistry: ");
       marks[1]=sc.nextInt();
       System.out.print("Enter marks of Maths: ");
       marks[2]=sc.nextInt();
}
```

So in this piece of code we are using array for 3 definite subjects. And we're using scanner class to input from the user. Let's say in the future I want to add a couple of more subject. So coding it again would not make it any flexible.

So I read that we could use arrayList, How can I use scanner class with arrayList similar to this piece of code.

答:
You could do something like this:

```<code>    public static void main(String[] args) {
        Scanner sc = new Scanner(System.in);
        List<Integer> list = new ArrayList<>();
        System.out.println("How many marks to enter?");
        int marksToEnter = sc.nextInt();

        for (int i = 0; i < marksToEnter; i++) {
            System.out.println("Enter next mark");
            list.add(sc.nextInt());
        }
        System.out.println(list);
    }
</code>```

Body:
public void getDisMarks()
   {
       marks=new int[3];
       System.out.print("Enter marks of Physics: ");
       marks[0]=sc.nextInt();
       System.out.print("Enter marks of Chemistry: ");
       marks[1]=sc.nextInt();
       System.out.print("Enter marks of Maths: ");
       marks[2]=sc.nextInt();
}

So in this piece of code we are using array for 3 definite subjects. And we're using scanner class to input from the user. Let's say in the future I want to add a couple of more subject. So coding it again would not make it any flexible.

So I read that we could use arrayList, How can I use scanner class with arrayList similar to this piece of code.

Body:
You could do something like this:

    public static void main(String[] args) {
        Scanner sc = new Scanner(System.in);
        List<Integer> list = new ArrayList<>();
        System.out.println("How many marks to enter?");
        int marksToEnter = sc.nextInt();

        for (int i = 0; i < marksToEnter; i++) {
            System.out.println("Enter next mark");
            list.add(sc.nextInt());
        }
        System.out.println(list);
    }


问:
Complex SQL to LINQ conversion with subquery
I am trying to convert this expression into LINQ from SQL, but a bit too difficult for me, maybe you can help me with this!</p>

```<code>SELECT     TOP (2) RecipeID, UserID, Name, Servings, PreparationTime, TotalTime, DifficultyLevelID, CuisineID, DishID, MainIngredientID, PriceLevelID, FlavourID, Instructions,
                      Notes, Thumbnail, VideoLink
FROM         dbo.Recipes
WHERE     (RecipeID NOT IN
                          (SELECT DISTINCT Recipes_1.RecipeID
                            FROM          dbo.Allergies INNER JOIN
                                                   dbo.UsersAllergies ON dbo.Allergies.AllergyID = dbo.UsersAllergies.AllergyID INNER JOIN
                                                   dbo.IngredientsAllergies ON dbo.Allergies.AllergyID = dbo.IngredientsAllergies.AllergyID INNER JOIN
                                                   dbo.Ingredients ON dbo.IngredientsAllergies.IngredientID = dbo.Ingredients.IngredientID INNER JOIN
                                                   dbo.RecipesIngredients ON dbo.Ingredients.IngredientID = dbo.RecipesIngredients.IngredientID INNER JOIN
                                                   dbo.Recipes AS Recipes_1 ON dbo.RecipesIngredients.RecipeID = Recipes_1.RecipeID INNER JOIN
                                                   dbo.Users ON dbo.UsersAllergies.UserID = dbo.Users.UserID INNER JOIN
                                                   dbo.AllergyFactors ON dbo.IngredientsAllergies.AllergyFactorID = dbo.AllergyFactors.AllergyFactorID
                            WHERE      (dbo.Users.UserID = 3) AND (dbo.AllergyFactors.AllergyFactorID < 3)))
</code>```

答:
It would be easier to help you if you showed us what you have already tried, but a Linq expression like this should give you the same result set</p>

```<code>var query = (from rec in context.Recipes
            where !(from al in context.Allergies
                    from ua in context.UsersAllergies.Where(x => al.AllergyID == x.AllergyID)
                    from ia in context.IngredientsAllergies.Where(x => al.AllergyID == x.AllergyID)
                    from in in context.Ingredients.Where(x => ia.IngredientID == x.IngredientID)
                    from ri in context.RecipesIngredients.Where(x => in.IngredientID == x.IngredientID)
                    from re in context.Recipes.Where(x => ri.RecipeID == x.RecipeID)
                    from us in context.Users.Where(x => ua.UserID == x.UserID)
                    from af in context.AllergyFactors.Where(x => ia.AllergyFactorID == x.AllergyFactorID)
                    where us.UserID == 3 && af.AllergyFactorID < 3
                    select re.RecipeID)
                    .Distinct()
                    .Contains(rec.RecipeID)
            select new
            {
                rec.RecipeID,
                rec.UserID,
                rec.Name,
                rec.Servings,
                rec.PreparationTime,
                rec.TotalTime,
                rec.DifficultyLevelID,
                rec.CuisineID,
                rec.DishID,
                rec.MainIngredientID,
                rec.PriceLevelID,
                rec.FlavourID,
                rec.Instructions,
                rec.Notes,
                rec.Thumbnail,
                rec.VideoLink
            }).Take(2);
</code>```

Body:
I am trying to convert this expression into LINQ from SQL, but a bit too difficult for me, maybe you can help me with this!

SELECT     TOP (2) RecipeID, UserID, Name, Servings, PreparationTime, TotalTime, DifficultyLevelID, CuisineID, DishID, MainIngredientID, PriceLevelID, FlavourID, Instructions,
                      Notes, Thumbnail, VideoLink
FROM         dbo.Recipes
WHERE     (RecipeID NOT IN
                          (SELECT DISTINCT Recipes_1.RecipeID
                            FROM          dbo.Allergies INNER JOIN
                                                   dbo.UsersAllergies ON dbo.Allergies.AllergyID = dbo.UsersAllergies.AllergyID INNER JOIN
                                                   dbo.IngredientsAllergies ON dbo.Allergies.AllergyID = dbo.IngredientsAllergies.AllergyID INNER JOIN
                                                   dbo.Ingredients ON dbo.IngredientsAllergies.IngredientID = dbo.Ingredients.IngredientID INNER JOIN
                                                   dbo.RecipesIngredients ON dbo.Ingredients.IngredientID = dbo.RecipesIngredients.IngredientID INNER JOIN
                                                   dbo.Recipes AS Recipes_1 ON dbo.RecipesIngredients.RecipeID = Recipes_1.RecipeID INNER JOIN
                                                   dbo.Users ON dbo.UsersAllergies.UserID = dbo.Users.UserID INNER JOIN
                                                   dbo.AllergyFactors ON dbo.IngredientsAllergies.AllergyFactorID = dbo.AllergyFactors.AllergyFactorID
                            WHERE      (dbo.Users.UserID = 3) AND (dbo.AllergyFactors.AllergyFactorID < 3)))

Body:
It would be easier to help you if you showed us what you have already tried, but a Linq expression like this should give you the same result set

var query = (from rec in context.Recipes
            where !(from al in context.Allergies
                    from ua in context.UsersAllergies.Where(x => al.AllergyID == x.AllergyID)
                    from ia in context.IngredientsAllergies.Where(x => al.AllergyID == x.AllergyID)
                    from in in context.Ingredients.Where(x => ia.IngredientID == x.IngredientID)
                    from ri in context.RecipesIngredients.Where(x => in.IngredientID == x.IngredientID)
                    from re in context.Recipes.Where(x => ri.RecipeID == x.RecipeID)
                    from us in context.Users.Where(x => ua.UserID == x.UserID)
                    from af in context.AllergyFactors.Where(x => ia.AllergyFactorID == x.AllergyFactorID)
                    where us.UserID == 3 && af.AllergyFactorID < 3
                    select re.RecipeID)
                    .Distinct()
                    .Contains(rec.RecipeID)
            select new
            {
                rec.RecipeID,
                rec.UserID,
                rec.Name,
                rec.Servings,
                rec.PreparationTime,
                rec.TotalTime,
                rec.DifficultyLevelID,
                rec.CuisineID,
                rec.DishID,
                rec.MainIngredientID,
                rec.PriceLevelID,
                rec.FlavourID,
                rec.Instructions,
                rec.Notes,
                rec.Thumbnail,
                rec.VideoLink
            }).Take(2);


问:
Open File Password Prompt While Closing
I have two separated workbooks with macroces: Book1 with Macro1 and Book2 with Macro2.
Macro1 is simple code to:

hidely open Book2 (has an open password: 1111);

write "test" in A1 Active Sheet of Book2;

run Macro2 from Book2, which is simple MsgBox "Hello! I am an msgbox
from Book2";

close Book2 with saving.

```<code>Sub Macro1()
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayAlerts = False

Dim myWb As Workbook
Set myWb = Workbooks.Open(ThisWorkbook.Path & "/" & "Book2.xlsb", True, False, , "1111")
myWb.Unprotect ("1111")

Cells(1, 1) = "test"

myWb.IsAddin = True
Application.Run "'Book2.xlsb'!Macro2"
myWb.Close savechanges:=True

Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
</code>```
```<code>Sub Macro2()
MsgBox "Hello! I am an msgbox from Book2"
End Sub
</code>```

I emphasize that everything should be done secretly and the user should not see neither Book2 nor any flashes, etc.
But when code comes to the saving step a window pops up on the screen, which, by the way, can be ignored and the code will continue to run/execute but anyway it's a PROBLEM I want get rid of:
password pop-up window

What I noticed:

If I remove the opening password from book2, the saving will be successfull and without pop-up dialogs. But I need Book2 opening protected.

I tried to modify code, but results are not unsatisfactory due to
screen blinking, screenshots below:
OK
V1-NOK
V2-NOK

V1: myWb.IsAddin = True is deleted/commented

```<code>Application.Run "'Book2.xlsb'!Macro2"
myWb.Close savechanges:=True
</code>```

V2: ON/OFF IsAddin = True before/after Macro2 executing

```<code>myWb.IsAddin = True
Application.Run "'Book2.xlsb'!Macro2"
myWb.IsAddin = False
myWb.Close savechanges:=True
</code>```

答:
Please, try the next way. It will open the workbook in a new session, will write in its first sheet and run the macro. But a macro sending a message from a non visible session/window is not the best idea. In order to see the message, please move the cursor over the Excel workbooks icons on taskbar, select the MsgBox window and press OK. A better idea would be do avoid sending messages. The code should run as you need:

```<code>Sub HiddenWBOpenRunMacro()
  Dim newEx As Excel.Application, myWb As Workbook

  Set newEx = CreateObject("Excel.Application")
  'Open the workbook in the newly created session:
  Set myWb = newEx.Workbooks.Open(ThisWorkbook.Path & "/" & "Book2.xlsb", , , , "1111")

  myWb.Sheets(1).cells(1, 1).Value = "Test"  'write in its first sheet
  myWb.Application.Run "'Book2.xlsb'!Macro2" 'run the macro

  myWb.Close True                            'close the workbook, saving it
  newEx.Quit                                 'quit the session
End Sub
</code>```

If ThisWorkbook not in Excel Trusted Locations it must be added.

Title: Open File Password Prompt While Closing

Body:
I have two separated workbooks with macroces: Book1 with Macro1 and Book2 with Macro2.
Macro1 is simple code to:

hidely open Book2 (has an open password: 1111);

write "test" in A1 Active Sheet of Book2;

run Macro2 from Book2, which is simple MsgBox "Hello! I am an msgbox
from Book2";

close Book2 with saving.

Sub Macro1()
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayAlerts = False

Dim myWb As Workbook
Set myWb = Workbooks.Open(ThisWorkbook.Path & "/" & "Book2.xlsb", True, False, , "1111")
myWb.Unprotect ("1111")

Cells(1, 1) = "test"

myWb.IsAddin = True
Application.Run "'Book2.xlsb'!Macro2"
myWb.Close savechanges:=True

Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

Sub Macro2()
MsgBox "Hello! I am an msgbox from Book2"
End Sub

I emphasize that everything should be done secretly and the user should not see neither Book2 nor any flashes, etc.
But when code comes to the saving step a window pops up on the screen, which, by the way, can be ignored and the code will continue to run/execute but anyway it's a PROBLEM I want get rid of:
password pop-up window

What I noticed:

If I remove the opening password from book2, the saving will be successfull and without pop-up dialogs. But I need Book2 opening protected.

I tried to modify code, but results are not unsatisfactory due to
screen blinking, screenshots below:
OK
V1-NOK
V2-NOK

V1: myWb.IsAddin = True is deleted/commented

Application.Run "'Book2.xlsb'!Macro2"
myWb.Close savechanges:=True

V2: ON/OFF IsAddin = True before/after Macro2 executing

myWb.IsAddin = True
Application.Run "'Book2.xlsb'!Macro2"
myWb.IsAddin = False
myWb.Close savechanges:=True

Body:
Please, try the next way. It will open the workbook in a new session, will write in its first sheet and run the macro. But a macro sending a message from a non visible session/window is not the best idea. In order to see the message, please move the cursor over the Excel workbooks icons on taskbar, select the MsgBox window and press OK. A better idea would be do avoid sending messages. The code should run as you need:

Sub HiddenWBOpenRunMacro()
  Dim newEx As Excel.Application, myWb As Workbook

  Set newEx = CreateObject("Excel.Application")
  'Open the workbook in the newly created session:
  Set myWb = newEx.Workbooks.Open(ThisWorkbook.Path & "/" & "Book2.xlsb", , , , "1111")

  myWb.Sheets(1).cells(1, 1).Value = "Test"  'write in its first sheet
  myWb.Application.Run "'Book2.xlsb'!Macro2" 'run the macro

  myWb.Close True                            'close the workbook, saving it
  newEx.Quit                                 'quit the session
End Sub

If ThisWorkbook not in Excel Trusted Locations it must be added.


问:
Flutter/Dart - I need second screen to show nested section of JSON that matches parameter recieved from first screen
In my app I have a main screen that has a series of clickable ListTiles listing dangerous snakes (with just a title and image).

These ListTiles will have an onTap() function calling MaterialPageRoute that sends a string type parameter to a second screen that is supposed to load the JSON details for the chosen snake using this parameter. The Parameter is called taxonString.

IE:

```<code>onTap: () {Navigator.push(context, MaterialPageRoute(builder: (context) => SpeciesScreen(taxonString: "mozambiqueSpittingCobra"),),);}
</code>```

I can receive this parameter no problem and display it in in a widget, ie:

```<code>  Text(widget.taxonString),
</code>```

The problem is - I have a JSON file that lists the details of each snake (nested under 'species' key) and the KEY for each species within the JSON file must match the received taxonString string.

My JSON:

```<code>{"species":
  {
    "mozambiqueSpittingCobra":
      {
        "commonName": "Mozambique Spitting Cobra",
        "latinName": "Naja mossambica",
        "swaziName": "Imfeti",
        "distributionDanger": "The Mozambique spitting cobra is found...",
        "habits": "habits long text.",
        "description": "description long text....",
        "behaviour": "behaviourfirstAid",
        "firstAid": "firstAidfirstAid",
        "biteSymptoms": [array of strings]
      },
    "vineSnake":
    {
      "commonName": "Vine Snake",
      "latinName": "Thelotornis capensis",
      "swaziName": "Indlolo",
      "distributionDanger": "The Vine Snake is found throughout Eswatini...",
        "habits": "habits long text.",
        "description": "description long text....",
        "behaviour": "behaviourfirstAid",
        "firstAid": "firstAidfirstAid",
        "biteSymptoms": [array of strings]
    },
    "snoutedCobra":
    {
      "commonName": "Snouted Cobra",
      "latinName": "Naja annulifera",
      "swaziName": "Imfeti",
      "distributionDanger": "The Snouted cobra is found throughout ....",
        "habits": "habits long text.",
        "description": "description long text....",
        "behaviour": "behaviourfirstAid",
        "firstAid": "firstAidfirstAid",
        "biteSymptoms": [array of strings]
    },
    //Extra JSON removed for brevity...
  }
}
</code>```

The taxonString value, in this case 'mozambiqueSpittingCobra', MUST load ONLY the portion of nested JSON matching the received taxonString parameter to the same KEY in the JSON.

I have spent over a week researching and trying different things mainly here in SO.

I CAN get the correct species to display if I hard code the species KEY into the details page, ie:

```<code>  factory SpeciesClass.fromJson(Map<String, dynamic> json) => SpeciesClass(
      thisTaxon: TaxonClass.fromJson(json['mozambiqueSpittingCobra'])
  );
</code>```

I tried to use a variable that holds the value of the received parameter but no luck, ie:

```<code>  factory SpeciesClass.fromJson(Map<String, dynamic> json) => SpeciesClass(
      thisTaxon: TaxonClass.fromJson(json[widget.taxonString])
  );
</code>```

I was hoping I could use the passed parameter from the first page like a variable, or use a WHERE condition saying something like..

```<code> load species fromJason Where species = widget.taxonString
</code>```

I hope I gave enough info.

Please shed some light.

I am new to Dart/Flutter (and OOP) - but as a web developer I am comfortable with code (Vanilla PHP, Javascript, ASP).
I DO NOT have experience with JSON but I am learning.

答: Try removing the keyword factory before you function definition

Body:
In my app I have a main screen that has a series of clickable ListTiles listing dangerous snakes (with just a title and image).

These ListTiles will have an onTap() function calling MaterialPageRoute that sends a string type parameter to a second screen that is supposed to load the JSON details for the chosen snake using this parameter. The Parameter is called taxonString.

IE:

onTap: () {Navigator.push(context, MaterialPageRoute(builder: (context) => SpeciesScreen(taxonString: "mozambiqueSpittingCobra"),),);}

I can receive this parameter no problem and display it in in a widget, ie:

  Text(widget.taxonString),

The problem is - I have a JSON file that lists the details of each snake (nested under 'species' key) and the KEY for each species within the JSON file must match the received taxonString string.

My JSON:

{"species":
  {
    "mozambiqueSpittingCobra":
      {
        "commonName": "Mozambique Spitting Cobra",
        "latinName": "Naja mossambica",
        "swaziName": "Imfeti",
        "distributionDanger": "The Mozambique spitting cobra is found...",
        "habits": "habits long text.",
        "description": "description long text....",
        "behaviour": "behaviourfirstAid",
        "firstAid": "firstAidfirstAid",
        "biteSymptoms": [array of strings]
      },
    "vineSnake":
    {
      "commonName": "Vine Snake",
      "latinName": "Thelotornis capensis",
      "swaziName": "Indlolo",
      "distributionDanger": "The Vine Snake is found throughout Eswatini...",
        "habits": "habits long text.",
        "description": "description long text....",
        "behaviour": "behaviourfirstAid",
        "firstAid": "firstAidfirstAid",
        "biteSymptoms": [array of strings]
    },
    "snoutedCobra":
    {
      "commonName": "Snouted Cobra",
      "latinName": "Naja annulifera",
      "swaziName": "Imfeti",
      "distributionDanger": "The Snouted cobra is found throughout ....",
        "habits": "habits long text.",
        "description": "description long text....",
        "behaviour": "behaviourfirstAid",
        "firstAid": "firstAidfirstAid",
        "biteSymptoms": [array of strings]
    },
    //Extra JSON removed for brevity...
  }
}

The taxonString value, in this case 'mozambiqueSpittingCobra', MUST load ONLY the portion of nested JSON matching the received taxonString parameter to the same KEY in the JSON.

I have spent over a week researching and trying different things mainly here in SO.

I CAN get the correct species to display if I hard code the species KEY into the details page, ie:

  factory SpeciesClass.fromJson(Map<String, dynamic> json) => SpeciesClass(
      thisTaxon: TaxonClass.fromJson(json['mozambiqueSpittingCobra'])
  );

I tried to use a variable that holds the value of the received parameter but no luck, ie:

  factory SpeciesClass.fromJson(Map<String, dynamic> json) => SpeciesClass(
      thisTaxon: TaxonClass.fromJson(json[widget.taxonString])
  );

I was hoping I could use the passed parameter from the first page like a variable, or use a WHERE condition saying something like..

 load species fromJason Where species = widget.taxonString

I hope I gave enough info.

Please shed some light.

I am new to Dart/Flutter (and OOP) - but as a web developer I am comfortable with code (Vanilla PHP, Javascript, ASP).
I DO NOT have experience with JSON but I am learning.


问:
.NET 5.0 SettingsSerializeAs.Binary obsolete
```<code>    /// Serialization
    /// Code 2012.05.23, [...] following Jani Giannoudis' examples
    /// CodeProject Article "User Settings Applied",
    /// http://www.codeproject.com/Articles/25829/User-Settings-Applied
    /// </summary>
</code>```

I'm using the above mentioned codeproject.com Code since a number of years successfully in different projects.
A few days ago, I converted one of those projects from .NET 4.x to .NET 6.0 and the unmodified code immediately stopped working (details below) for example in the following snippet:

```<code>        // DataGridColumnSetting[] is based on System.Configuration.ApplicationSettingsBase
        // https://learn.microsoft.com/en-us/dotnet/api/system.configuration.applicationsettingsbase?view=dotnet-plat-ext-6.0
        private DataGridColumnSetting[] OriginalColumnSettings
        {
            get
            {
                return LoadValue(
                    Name,
                    typeof(DataGridColumnSetting[]),
                    SettingsSerializeAs.Binary,
                    null) as DataGridColumnSetting[];
            }
        }
</code>```

Throwing a

```<code>System.NotSupportedException
  HResult=0x80131515
  Message=BinaryFormatter serialization is obsolete and should not be used. See https://aka.ms/binaryformatter for more information.
  Source=System.Configuration.ConfigurationManager
  StackTrace:
   at System.Configuration.SettingsProperty..ctor(String name, Type propertyType, SettingsProvider provider, Boolean isReadOnly, Object defaultValue, SettingsSerializeAs serializeAs, SettingsAttributeDictionary attributes, Boolean throwOnErrorDeserializing, Boolean throwOnErrorSerializing)
   at MyNamespace.Serialization.Setting.CreateSettingProperty(String name, Type type, SettingsSerializeAs serializeAs, Object defaultValue) in [...]MyNamespace\Serialization\Setting.cs:line 111
</code>```

Since the very same code is working well in a .NET 4.8 project, I tried to find hints in the web and found

https://learn.microsoft.com/en-us/dotnet/core/compatibility/core-libraries/5.0/binaryformatter-serialization-obsolete
(and a few others) also saying

Warning
"The BinaryFormatter type is dangerous and is not recommended for data processing. Applications should stop using BinaryFormatter as soon as possible, even if they believe the data they're processing to be trustworthy. BinaryFormatter is insecure and can't be made secure."

Actual Question:
Anyone else having the very same issue using the same code (from the above mentioned CodeProject Article "User Settings Applied").
(If not, I would start modifying (my personal flavor of) that code, and if successful post an answer to my question assuming others might hopefully benefit.)

答:
Okay, apparently using JSON instead of Binary works as expected here in an http://www.codeproject.com/Articles/25829/User-Settings-Applied context (in .Net4.x as well as .Net 6).
The basic concept there is having a particular Serialization class for each UI Control one wants to handle. And only some of the article samples are using the deprecated SettingsSerializeAs.Binary, for example the one made for WPF DataGridcontrols. The concept modification that works for me is using (NuGet) Newtonsoft.Json for serialization there.
The typical pattern part where the article author is using SettingsSerializeAs as quoted in the question is now using SettingsSerializeAs.String instead of Binary:

```<code>private string OriginalColumnSettings
{
    get
    {
        return LoadValue(
                    Name,
                    typeof(string),
                    SettingsSerializeAs.String,
                    defaultValue: null) as string;
    }
}
</code>```

Full real world (WPF) Sample for both .Net4.8 as well as .Net6:

```<code>using Newtonsoft.Json;
using NLog;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Globalization;
using System.Linq;
using System.Windows;
using System.Windows.Controls;

namespace WhatEverNamespace.Serialization
{
    /// <summary>
    /// Serialization
    /// Code 2012.05.23, [...] following Jani Giannoudis' examples
    /// CodeProject Article "User Settings Applied",
    /// http://www.codeproject.com/Articles/25829/User-Settings-Applied
    /// </summary>
    public class DataGridSetting : Setting
    {
        public static readonly DependencyProperty SettingProperty =
            DependencyProperty.RegisterAttached(
                "Setting",
                typeof(string),
                typeof(DataGridSetting),
                new FrameworkPropertyMetadata(OnDataGridSettingChanged));

        #region Fields

        private static readonly Logger log = LogManager.GetCurrentClassLogger();

        private readonly DataGrid dataGrid;
        private readonly IList<DataGridColumn> dataGridColumns = new List<DataGridColumn>();
        private bool isLoaded;

        private readonly string name;
        private bool useWidth = true;
        private bool useDisplayIndex = true;

        #endregion Fields

        #region Constructors

        public DataGridSetting(DataGrid dataGrid) :
            this(dataGrid?.Name, dataGrid)
        {
        }

        public DataGridSetting(string name, DataGrid dataGrid)
        {
            if (string.IsNullOrEmpty(name))
                throw new ArgumentNullException(nameof(name));

            this.name = name;
            this.dataGrid = dataGrid ?? throw new ArgumentNullException(nameof(dataGrid));
        }

        #endregion Constructors

        #region Properties

        public string Name { get { return name; } }

        public DataGrid DataGrid { get { return dataGrid; } }

        public bool UseWidth
        {
            get { return useWidth; }
            set { useWidth = value; }
        }

        public bool UseDisplayIndex
        {
            get { return useDisplayIndex; }
            set { useDisplayIndex = value; }
        }

        public override bool HasChanged
        {
            get
            {
                var json = OriginalColumnSettings;
                if (string.IsNullOrWhiteSpace(json))
                    return false;

                var originalColumnSettings = JsonConvert.DeserializeObject<DataGridColumnSetting[]>(json);
                DataGridColumnSetting[] columnSettings = ColumnSettings;
                if (json.Length != columnSettings.Length)
                    return true;

                for (int i = 0; i < originalColumnSettings.Length; i++)
                {
                    if (!originalColumnSettings[i].Equals(columnSettings[i]))
                        return true;
                }

                return false;
            }
        }

        private string OriginalColumnSettings
        {
            get
            {
                return LoadValue(
                    Name,
                    typeof(string),
                    SettingsSerializeAs.String,
                    defaultValue: null) as string;
            }
        }

        private DataGridColumnSetting[] ColumnSettings
        {
            get
            {
                if (dataGrid?.Columns.Any() != true)
                    return null;

                IList<DataGridColumnSetting> columnSettings =
                    new List<DataGridColumnSetting>(dataGrid.Columns.Count);
                foreach (DataGridColumn dataGridColumn in dataGrid.Columns)
                {
                    int index = dataGridColumns.IndexOf(dataGridColumn);
                    int displayIndex = dataGridColumn.DisplayIndex;
                    DataGridColumnSetting columnSetting = new DataGridColumnSetting
                    {
                        Index = index,
                        DisplayIndex = displayIndex,
                        Width = dataGridColumn.ActualWidth
                    };
                    columnSettings.Add(columnSetting);
                }

                return columnSettings.ToArray();
            }
        }

        #endregion Properties

        #region Methods

        public static string GetSetting(DependencyObject dependencyObject)
        {
            return dependencyObject?.GetValue(SettingProperty) as string;
        }

        public static void SetSetting(DependencyObject dependencyObject, string settingKey)
        {
            dependencyObject?.SetValue(SettingProperty, settingKey);
        }

        public override void Load()
        {
            // Initialized event does not work since it's running too early in a WPF DataGrid
            // ("dataGrid.Initialized += DataGridInitialized" in Jani's ListViewSettings.cs)
            if (isLoaded == false)
                SetupDataGridColumns();

            try
            {
                DataGrid dataGrid = this.dataGrid;
                if (dataGrid?.Columns.Any() != true)
                    return;

                var json = OriginalColumnSettings;
                var columnSettings = JsonConvert.DeserializeObject<DataGridColumnSetting[]>(json);
                if (columnSettings?.Any() != true)
                    return;

                for (int displayIndex = 0; displayIndex < columnSettings.Length; displayIndex++)
                {
                    DataGridColumnSetting columnSetting = columnSettings[displayIndex];
                    if (columnSetting.Index < 0 || columnSetting.Index >= dataGridColumns.Count)
                        continue;

                    DataGridColumn dataGridColumn = dataGridColumns[columnSetting.Index];
                    if (useWidth)
                        dataGridColumn.Width = new DataGridLength(columnSetting.Width);

                    if (useDisplayIndex && columnSetting.Index != columnSetting.DisplayIndex)
                        dataGridColumn.DisplayIndex = columnSetting.DisplayIndex;
                }
            }
            catch
            {
                if (ThrowOnErrorLoading)
                    throw;
            }
        }

        public override void Save()
        {
            try
            {
                DataGridColumnSetting[] columnSettings = ColumnSettings;
                if (columnSettings == null)
                    return;

                var json = JsonConvert.SerializeObject(columnSettings);

                SaveValue(
                    Name,
                    typeof(string),
                    SettingsSerializeAs.String,
                    json,
                    null);
            }
            catch
            {
                if (ThrowOnErrorSaving)
                    throw;
            }
        }

        public override string ToString()
        {
            return string.Concat(name, " (DataGrid)");
        }

        private void SetupDataGridColumns()
        {
            dataGridColumns.Clear();

            if (dataGrid == null)
                return;

            if (dataGrid.Columns.Count > 0)
                isLoaded = true;
            else
                return;

            for (int i = 0; i < dataGrid.Columns.Count; i++)
            {
                dataGridColumns.Add(dataGrid.Columns[i]);
            }
        }

        private static void OnDataGridSettingChanged(DependencyObject dependencyObject, DependencyPropertyChangedEventArgs e)
        {
            if (!(dependencyObject is DataGrid dataGrid))
            {
                log.Warn(CultureInfo.InvariantCulture,
                    "{0}.{1}(): invalid property attachment",
                    nameof(DataGridSetting),
                    nameof(OnDataGridSettingChanged));
                return;
            }

            // search on the parent-tree for application settings
            ApplicationSettings applicationSettings = FindParentSettings(dependencyObject);
            if (applicationSettings == null)
            {
                log.Warn(CultureInfo.InvariantCulture,
                    "{0}.{1}(): missing application settings in parent hierarchy",
                    nameof(DataGridSetting),
                    nameof(OnDataGridSettingChanged));
                return;
            }

            applicationSettings.Settings.Add(new DataGridSetting(dataGrid));
        }

        private static ApplicationSettings FindParentSettings(DependencyObject element)
        {
            while (element != null)
            {
                if (element.ReadLocalValue(
                    DependencyPropertySetting.ApplicationSettingsProperty) is ApplicationSettings applicationSettings)
                    return applicationSettings;
                element = LogicalTreeHelper.GetParent(element);
            }
            return null;
        }

        #endregion Methods
    }
}
</code>```

Body:
    /// Serialization
    /// Code 2012.05.23, [...] following Jani Giannoudis' examples
    /// CodeProject Article "User Settings Applied",
    /// http://www.codeproject.com/Articles/25829/User-Settings-Applied
    ///

I'm using the above mentioned codeproject.com Code since a number of years successfully in different projects.
A few days ago, I converted one of those projects from .NET 4.x to .NET 6.0 and the unmodified code immediately stopped working (details below) for example in the following snippet:

        // DataGridColumnSetting[] is based on System.Configuration.ApplicationSettingsBase
        // https://learn.microsoft.com/en-us/dotnet/api/system.configuration.applicationsettingsbase?view=dotnet-plat-ext-6.0
        private DataGridColumnSetting[] OriginalColumnSettings
        {
            get
            {
                return LoadValue(
                    Name,
                    typeof(DataGridColumnSetting[]),
                    SettingsSerializeAs.Binary,
                    null) as DataGridColumnSetting[];
            }
        }

Throwing a

System.NotSupportedException
  HResult=0x80131515
  Message=BinaryFormatter serialization is obsolete and should not be used. See https://aka.ms/binaryformatter for more information.
  Source=System.Configuration.ConfigurationManager
  StackTrace:
   at System.Configuration.SettingsProperty..ctor(String name, Type propertyType, SettingsProvider provider, Boolean isReadOnly, Object defaultValue, SettingsSerializeAs serializeAs, SettingsAttributeDictionary attributes, Boolean throwOnErrorDeserializing, Boolean throwOnErrorSerializing)
   at MyNamespace.Serialization.Setting.CreateSettingProperty(String name, Type type, SettingsSerializeAs serializeAs, Object defaultValue) in [...]MyNamespace\Serialization\Setting.cs:line 111

Since the very same code is working well in a .NET 4.8 project, I tried to find hints in the web and found

https://learn.microsoft.com/en-us/dotnet/core/compatibility/core-libraries/5.0/binaryformatter-serialization-obsolete
(and a few others) also saying

Warning
"The BinaryFormatter type is dangerous and is not recommended for data processing. Applications should stop using BinaryFormatter as soon as possible, even if they believe the data they're processing to be trustworthy. BinaryFormatter is insecure and can't be made secure."

Actual Question:
Anyone else having the very same issue using the same code (from the above mentioned CodeProject Article "User Settings Applied").
(If not, I would start modifying (my personal flavor of) that code, and if successful post an answer to my question assuming others might hopefully benefit.)

Body:
Okay, apparently using JSON instead of Binary works as expected here in an http://www.codeproject.com/Articles/25829/User-Settings-Applied context (in .Net4.x as well as .Net 6).
The basic concept there is having a particular Serialization class for each UI Control one wants to handle. And only some of the article samples are using the deprecated SettingsSerializeAs.Binary, for example the one made for WPF DataGridcontrols. The concept modification that works for me is using (NuGet) Newtonsoft.Json for serialization there.
The typical pattern part where the article author is using SettingsSerializeAs as quoted in the question is now using SettingsSerializeAs.String instead of Binary:

private string OriginalColumnSettings
{
    get
    {
        return LoadValue(
                    Name,
                    typeof(string),
                    SettingsSerializeAs.String,
                    defaultValue: null) as string;
    }
}

Full real world (WPF) Sample for both .Net4.8 as well as .Net6:

using Newtonsoft.Json;
using NLog;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Globalization;
using System.Linq;
using System.Windows;
using System.Windows.Controls;

namespace WhatEverNamespace.Serialization
{
    /// <summary>
    /// Serialization
    /// Code 2012.05.23, [...] following Jani Giannoudis' examples
    /// CodeProject Article "User Settings Applied",
    /// http://www.codeproject.com/Articles/25829/User-Settings-Applied
    /// </summary>
    public class DataGridSetting : Setting
    {
        public static readonly DependencyProperty SettingProperty =
            DependencyProperty.RegisterAttached(
                "Setting",
                typeof(string),
                typeof(DataGridSetting),
                new FrameworkPropertyMetadata(OnDataGridSettingChanged));

        #region Fields

        private static readonly Logger log = LogManager.GetCurrentClassLogger();

        private readonly DataGrid dataGrid;
        private readonly IList<DataGridColumn> dataGridColumns = new List<DataGridColumn>();
        private bool isLoaded;

        private readonly string name;
        private bool useWidth = true;
        private bool useDisplayIndex = true;

        #endregion Fields

        #region Constructors

        public DataGridSetting(DataGrid dataGrid) :
            this(dataGrid?.Name, dataGrid)
        {
        }

        public DataGridSetting(string name, DataGrid dataGrid)
        {
            if (string.IsNullOrEmpty(name))
                throw new ArgumentNullException(nameof(name));

            this.name = name;
            this.dataGrid = dataGrid ?? throw new ArgumentNullException(nameof(dataGrid));
        }

        #endregion Constructors

        #region Properties

        public string Name { get { return name; } }

        public DataGrid DataGrid { get { return dataGrid; } }

        public bool UseWidth
        {
            get { return useWidth; }
            set { useWidth = value; }
        }

        public bool UseDisplayIndex
        {
            get { return useDisplayIndex; }
            set { useDisplayIndex = value; }
        }

        public override bool HasChanged
        {
            get
            {
                var json = OriginalColumnSettings;
                if (string.IsNullOrWhiteSpace(json))
                    return false;

                var originalColumnSettings = JsonConvert.DeserializeObject<DataGridColumnSetting[]>(json);
                DataGridColumnSetting[] columnSettings = ColumnSettings;
                if (json.Length != columnSettings.Length)
                    return true;

                for (int i = 0; i < originalColumnSettings.Length; i++)
                {
                    if (!originalColumnSettings[i].Equals(columnSettings[i]))
                        return true;
                }

                return false;
            }
        }

        private string OriginalColumnSettings
        {
            get
            {
                return LoadValue(
                    Name,
                    typeof(string),
                    SettingsSerializeAs.String,
                    defaultValue: null) as string;
            }
        }

        private DataGridColumnSetting[] ColumnSettings
        {
            get
            {
                if (dataGrid?.Columns.Any() != true)
                    return null;

                IList<DataGridColumnSetting> columnSettings =
                    new List<DataGridColumnSetting>(dataGrid.Columns.Count);
                foreach (DataGridColumn dataGridColumn in dataGrid.Columns)
                {
                    int index = dataGridColumns.IndexOf(dataGridColumn);
                    int displayIndex = dataGridColumn.DisplayIndex;
                    DataGridColumnSetting columnSetting = new DataGridColumnSetting
                    {
                        Index = index,
                        DisplayIndex = displayIndex,
                        Width = dataGridColumn.ActualWidth
                    };
                    columnSettings.Add(columnSetting);
                }

                return columnSettings.ToArray();
            }
        }

        #endregion Properties

        #region Methods

        public static string GetSetting(DependencyObject dependencyObject)
        {
            return dependencyObject?.GetValue(SettingProperty) as string;
        }

        public static void SetSetting(DependencyObject dependencyObject, string settingKey)
        {
            dependencyObject?.SetValue(SettingProperty, settingKey);
        }

        public override void Load()
        {
            // Initialized event does not work since it's running too early in a WPF DataGrid
            // ("dataGrid.Initialized += DataGridInitialized" in Jani's ListViewSettings.cs)
            if (isLoaded == false)
                SetupDataGridColumns();

            try
            {
                DataGrid dataGrid = this.dataGrid;
                if (dataGrid?.Columns.Any() != true)
                    return;

                var json = OriginalColumnSettings;
                var columnSettings = JsonConvert.DeserializeObject<DataGridColumnSetting[]>(json);
                if (columnSettings?.Any() != true)
                    return;

                for (int displayIndex = 0; displayIndex < columnSettings.Length; displayIndex++)
                {
                    DataGridColumnSetting columnSetting = columnSettings[displayIndex];
                    if (columnSetting.Index < 0 || columnSetting.Index >= dataGridColumns.Count)
                        continue;

                    DataGridColumn dataGridColumn = dataGridColumns[columnSetting.Index];
                    if (useWidth)
                        dataGridColumn.Width = new DataGridLength(columnSetting.Width);

                    if (useDisplayIndex && columnSetting.Index != columnSetting.DisplayIndex)
                        dataGridColumn.DisplayIndex = columnSetting.DisplayIndex;
                }
            }
            catch
            {
                if (ThrowOnErrorLoading)
                    throw;
            }
        }

        public override void Save()
        {
            try
            {
                DataGridColumnSetting[] columnSettings = ColumnSettings;
                if (columnSettings == null)
                    return;

                var json = JsonConvert.SerializeObject(columnSettings);

                SaveValue(
                    Name,
                    typeof(string),
                    SettingsSerializeAs.String,
                    json,
                    null);
            }
            catch
            {
                if (ThrowOnErrorSaving)
                    throw;
            }
        }

        public override string ToString()
        {
            return string.Concat(name, " (DataGrid)");
        }

        private void SetupDataGridColumns()
        {
            dataGridColumns.Clear();

            if (dataGrid == null)
                return;

            if (dataGrid.Columns.Count > 0)
                isLoaded = true;
            else
                return;

            for (int i = 0; i < dataGrid.Columns.Count; i++)
            {
                dataGridColumns.Add(dataGrid.Columns[i]);
            }
        }

        private static void OnDataGridSettingChanged(DependencyObject dependencyObject, DependencyPropertyChangedEventArgs e)
        {
            if (!(dependencyObject is DataGrid dataGrid))
            {
                log.Warn(CultureInfo.InvariantCulture,
                    "{0}.{1}(): invalid property attachment",
                    nameof(DataGridSetting),
                    nameof(OnDataGridSettingChanged));
                return;
            }

            // search on the parent-tree for application settings
            ApplicationSettings applicationSettings = FindParentSettings(dependencyObject);
            if (applicationSettings == null)
            {
                log.Warn(CultureInfo.InvariantCulture,
                    "{0}.{1}(): missing application settings in parent hierarchy",
                    nameof(DataGridSetting),
                    nameof(OnDataGridSettingChanged));
                return;
            }

            applicationSettings.Settings.Add(new DataGridSetting(dataGrid));
        }

        private static ApplicationSettings FindParentSettings(DependencyObject element)
        {
            while (element != null)
            {
                if (element.ReadLocalValue(
                    DependencyPropertySetting.ApplicationSettingsProperty) is ApplicationSettings applicationSettings)
                    return applicationSettings;
                element = LogicalTreeHelper.GetParent(element);
            }
            return null;
        }

        #endregion Methods
    }
}


问:
django.core.exceptions.FieldError: Cannot resolve keyword 'date' into field. Join on 'date' not permitted
```<code># Here is my models
</code>```

This is my CustmerBuySell model DB designed.

```<code>class CustomerBuySell(models.Model):
    customer = models.ForeignKey(CustomerAdd, on_delete=models.CASCADE)
    customer_buy_sell_debit = models.DecimalField(max_digits=10, decimal_places=2, default=0.00)
    customer_buy_sell_credit = models.DecimalField(max_digits=10, decimal_places=2, default=0.00)
    description = models.CharField(max_length=250, blank=True)
    date = models.DateField()
    sms = models.BooleanField(default=False)
    picture = models.ImageField(upload_to='customer_buy_sell_pics', default='images.png')

    created_at = models.DateTimeField(auto_now_add=True, blank=True, null=True)
    updated_at = models.DateTimeField(auto_now=True, blank=True, null=True)

    def __str__(self):
        return self.customer.customer_name

    class Meta:
        verbose_name = "Customer BuySell"
        verbose_name_plural = "Customer BuySell"

# Here, is my View.
</code>```

This is the class-based APIView, which I have used. And try to use the aggregate query in this view.

```<code>class DailyCustomerBuySellAPIView(APIView):

    def get(self, request):
        customer_buy_sell = CustomerBuySell.objects.extra(select={'day': 'date( date )'}).values('day').order_by(
            'date__date').annotate(available=Count('date__date'))
        serializer = CustomerBuySellSerializer(customer_buy_sell, many=True)
        return Response({"customer_buy_sell": serializer.data})

# And, finally here are my Serializers
</code>```

I have no idea what's the problem! Please help me.

```<code>class CustomerBuySellSerializer(serializers.ModelSerializer):
    # customer = CustomerAddSerializer()

    class Meta:
        model = CustomerBuySell
        fields = '__all__'

    def to_representation(self, instance):
        representation = super(CustomerBuySellSerializer, self).to_representation(instance)

        if instance.customer is not None:
            customer_name = instance.customer.customer_name
            previous_due = instance.customer.previous_due
            representation['custo`enter code here`mer_name'] = customer_name
            representation['previous_due'] = previous_due
            return representation
</code>```

答: I suppose it is just a typo: Change date__date to date</p>

Body:
# Here is my models

This is my CustmerBuySell model DB designed.

class CustomerBuySell(models.Model):
    customer = models.ForeignKey(CustomerAdd, on_delete=models.CASCADE)
    customer_buy_sell_debit = models.DecimalField(max_digits=10, decimal_places=2, default=0.00)
    customer_buy_sell_credit = models.DecimalField(max_digits=10, decimal_places=2, default=0.00)
    description = models.CharField(max_length=250, blank=True)
    date = models.DateField()
    sms = models.BooleanField(default=False)
    picture = models.ImageField(upload_to='customer_buy_sell_pics', default='images.png')

    created_at = models.DateTimeField(auto_now_add=True, blank=True, null=True)
    updated_at = models.DateTimeField(auto_now=True, blank=True, null=True)

    def __str__(self):
        return self.customer.customer_name

    class Meta:
        verbose_name = "Customer BuySell"
        verbose_name_plural = "Customer BuySell"

# Here, is my View.

This is the class-based APIView, which I have used. And try to use the aggregate query in this view.

class DailyCustomerBuySellAPIView(APIView):

    def get(self, request):
        customer_buy_sell = CustomerBuySell.objects.extra(select={'day': 'date( date )'}).values('day').order_by(
            'date__date').annotate(available=Count('date__date'))
        serializer = CustomerBuySellSerializer(customer_buy_sell, many=True)
        return Response({"customer_buy_sell": serializer.data})

# And, finally here are my Serializers

I have no idea what's the problem! Please help me.

class CustomerBuySellSerializer(serializers.ModelSerializer):
    # customer = CustomerAddSerializer()

    class Meta:
        model = CustomerBuySell
        fields = '__all__'

    def to_representation(self, instance):
        representation = super(CustomerBuySellSerializer, self).to_representation(instance)

        if instance.customer is not None:
            customer_name = instance.customer.customer_name
            previous_due = instance.customer.previous_due
            representation['custo`enter code here`mer_name'] = customer_name
            representation['previous_due'] = previous_due
            return representation


问:
How to achieve the following? C++ "advanced" map/list/array?
I am trying to create some sort of list, map or what ever may be suggested that does the following

```<code>something [] = {
        mainvalue1 = 2001 {
            value2 = 1905
            value3 = 2000
            result = 500

            value2 = 1910
            value3 = 2030
            result = 700
        }
        mainvalue2 = 2005 {
            value2 = 1635
            value3 = 2070
            result = 300

            value2 = 2310
            value3 = 5930
            result = 599
        }
}
</code>```

cout << "result: " << something[2001][1905][2000] << endl;

result: 500

cout << "result: " something[2001][1910][2030] << endl;

result: 700

cout << "result: " something[2005][2310][5930] << endl;

result: 599

Is this possible? I would appreciate if someone could guide me how to start my thought process or give me an example how this can be achievable in C++

答:
Try this</p>
```<code>#include <iostream>
#include <map>

using namespace std;

int main()
{
    map<int, map<int, map<int, int>>> something;
    something[2001][1905][2000] = 500;
    something[2001][1910][2030] = 700;
    something[2005][2310][5930] = 599;

    cout << "result: " << something[2001][1905][2000] << endl;
    cout << "result: " << something[2001][1910][2030] << endl;
    cout << "result: " << something[2005][2310][5930] << endl;

    return 0;
}
</code>```

Body:
I am trying to create some sort of list, map or what ever may be suggested that does the following

something [] = {
        mainvalue1 = 2001 {
            value2 = 1905
            value3 = 2000
            result = 500

            value2 = 1910
            value3 = 2030
            result = 700
        }
        mainvalue2 = 2005 {
            value2 = 1635
            value3 = 2070
            result = 300

            value2 = 2310
            value3 = 5930
            result = 599
        }
}

cout << "result: " << something[2001][1905][2000] << endl;

result: 500

cout << "result: " something[2001][1910][2030] << endl;

result: 700

cout << "result: " something[2005][2310][5930] << endl;

result: 599

Is this possible? I would appreciate if someone could guide me how to start my thought process or give me an example how this can be achievable in C++

Body:
Try this

#include <iostream>
#include <map>

using namespace std;

int main()
{
    map<int, map<int, map<int, int>>> something;
    something[2001][1905][2000] = 500;
    something[2001][1910][2030] = 700;
    something[2005][2310][5930] = 599;

    cout << "result: " << something[2001][1905][2000] << endl;
    cout << "result: " << something[2001][1910][2030] << endl;
    cout << "result: " << something[2005][2310][5930] << endl;

    return 0;
}