问:
Parsing multiple jpegs stored contiguously in a stream
I've got a stream which can contain either one or more images, stored contiguously with no seperatng flags. When I use javas ImageIO to open and parse the stream it parses the first image correctly, but it closes the stream, which makes me unable to stream the next image.

So I'm trying to figure out how large each compressed image in the file is, so that I can read the exact number of bytes for that image into a buffer, create a ByteArrayInputStream out of that buffer, and create the BufferedImage using the new smaller buffer. My problem is figuring out how large the image is, width/height don't help since compressed are smaller than width * height.

Is there a way to read through the buffer and find out where each image ends easily? There are no soi, eoi, sof, eof, tags throughout except one eof at the end of the stream.

Or is there a better way to do this? A way to ImageIO.read() that won't close the stream and let me keep working with it?

答:
First of all, ImageIO.read(...) is a convenience method, that can only ever read a single (the first) image of any file in any format.

If, instead, you obtain an ImageReader for your input, you can (generally) get the number of images in the input using getNumImages(boolean allowSearch), and read (or skip) individual images using read(int imageIndex, ImageReadParam param).

Something like:

```<code>// Create input stream
try (ImageInputStream input = ImageIO.createImageInputStream(file)) {
    // Get the reader
    Iterator<ImageReader> readers = ImageIO.getImageReaders(input);

    if (!readers.hasNext()) {
        throw new IllegalArgumentException("No reader for: " + file);
    }

    ImageReader reader = readers.next();

    try {
        reader.setInput(input);

        // Optionally, listen for read warnings, progress, etc.
        reader.addIIOReadWarningListener(...);
        reader.addIIOReadProgressListener(...);

        ImageReadParam param = reader.getDefaultReadParam();

        // Note: For some formats (ie. GIF), finding the number of images
        // requires searching the entire stream. In this case, it may
        // be more convenient to just read until you get an IndexOutOfBoundsException
        for (int i = 0; i < reader.getNumImages(true); i++) {
            // Optionally, control read settings like sub sampling, source region or destination etc.
            param.setSourceSubsampling(...);
            param.setSourceRegion(...);
            param.setDestination(...);
            // ...

            // Finally read the image, using settings from param
            BufferedImage image = reader.read(i, param);

            // Optionally, read thumbnails, meta data, etc...
            int numThumbs = reader.getNumThumbnails(0);
            // ...
        }
    }
    finally {
        // Dispose reader in finally block to avoid memory leaks
        reader.dispose();
    }
}
</code>```

The JRE-bundled JPEGImageReader should in theory also support multiple JFIF substreams in a single stream. However, I think you will find that the support is broken (at least I have an open issue to fix this in my ImageIO plugin project).

Unfortunately, finding the length of a JPEG compressed image without decoding (or having the length stored outside the JFIF stream), is not possible.

One thing you could try, is (possibly wrapping the ImageInputStream) to read the first image, then scan forward for the next SOI ("Start-of-Image", 0xffd8) marker, seek back two bytes (for the SOI marker) and attempt a read of the next image. The SOI marker can't exist inside the JPEG encoded data.

You can read more about JPEG and JFIF segments on Wikipedia.

Body:
First of all, ImageIO.read(...) is a convenience method, that can only ever read a single (the first) image of any file in any format.

If, instead, you obtain an ImageReader for your input, you can (generally) get the number of images in the input using getNumImages(boolean allowSearch), and read (or skip) individual images using read(int imageIndex, ImageReadParam param).

Something like:

// Create input stream
try (ImageInputStream input = ImageIO.createImageInputStream(file)) {
    // Get the reader
    Iterator<ImageReader> readers = ImageIO.getImageReaders(input);

    if (!readers.hasNext()) {
        throw new IllegalArgumentException("No reader for: " + file);
    }

    ImageReader reader = readers.next();

    try {
        reader.setInput(input);

        // Optionally, listen for read warnings, progress, etc.
        reader.addIIOReadWarningListener(...);
        reader.addIIOReadProgressListener(...);

        ImageReadParam param = reader.getDefaultReadParam();

        // Note: For some formats (ie. GIF), finding the number of images
        // requires searching the entire stream. In this case, it may
        // be more convenient to just read until you get an IndexOutOfBoundsException
        for (int i = 0; i < reader.getNumImages(true); i++) {
            // Optionally, control read settings like sub sampling, source region or destination etc.
            param.setSourceSubsampling(...);
            param.setSourceRegion(...);
            param.setDestination(...);
            // ...

            // Finally read the image, using settings from param
            BufferedImage image = reader.read(i, param);

            // Optionally, read thumbnails, meta data, etc...
            int numThumbs = reader.getNumThumbnails(0);
            // ...
        }
    }
    finally {
        // Dispose reader in finally block to avoid memory leaks
        reader.dispose();
    }
}

The JRE-bundled JPEGImageReader should in theory also support multiple JFIF substreams in a single stream. However, I think you will find that the support is broken (at least I have an open issue to fix this in my ImageIO plugin project).

Unfortunately, finding the length of a JPEG compressed image without decoding (or having the length stored outside the JFIF stream), is not possible.

One thing you could try, is (possibly wrapping the ImageInputStream) to read the first image, then scan forward for the next SOI ("Start-of-Image", 0xffd8) marker, seek back two bytes (for the SOI marker) and attempt a read of the next image. The SOI marker can't exist inside the JPEG encoded data.

You can read more about JPEG and JFIF segments on Wikipedia.


来源: stackexchange

问:
How to deal with vendor/plugins after upgrading to rails 3.2.1
After upgrading to rails3.2.1,this warning occurs:

You have Rails 2.3-style plugins in vendor/plugins! Support for these plugins will be removed in Rails 4.0. Move them out and bundle them in your Gemfile, or fold them in to your app as lib/myplugin/* and config/initializers/myplugin.rb. See the release notes for more on this: http://weblog.rubyonrails.org/2012/1/4/rails-3-2-0-rc2-has-been-released

I move my plugins in vendor/plugins directory but i don't know how to write  config/initializers/myplugin.rb file, and google can't find the answer.

答:
No one has mentioned converting to a Railtie or Rails::Engine.

Just move all of your ruby files into a gem [use bundle gem to create it for minimal friction].

Then have a look at the Railtie docs [and find your target Rails version]:

https://apidock.com/rails/v3.2.13/Rails/Railtie

It's quite easy to just convert an old plugin init.rb to a railtie in this way, and it gives you a pathway to Rails 4+.

Got views or controllers?, then use a Rails::Engine instead.   While engines in gems can be complicated from a workflow standpoint, converting a plugin to one is pretty trivial.

Do yourself a favor and start building tests right in the gem, instead of in the Rails project.

ContentLicense: CC BY-SA 3.0


问:
JavaScript Object vs minimongo efficiency
My Meteor client receives data from the server and stores it in minimongo. This data is guaranteed not to change during their session, so I don't need Meteor's reactivity. The static data just happens to arrive by that route; let's just take that as a given.

The data looks like this:

```<code>{_id: 'abc...', val: {...}}
</code>```

On the client, is it more efficient for me to look up values using:

```<code>val_I_need = Collection.findOne({id})
</code>```

or to create a JavaScript object:

```<code>data = {}
Collection.find().fetch().map((x) => {data[x._id] = x.val})
</code>```

and use it for look ups:

```<code>val_I_need = data[id]
</code>```

Is there a tipping point, either in terms of the size of the data or the number of look ups, where the more efficient method changes, or outweighs the initial cost of building the object?

答:
My gut feeling is that you'll never hit a point where the performance gain of putting it in an object makes a noticeable difference.

It's more likely that your bottleneck will be in the pub/sub mechanism, as it can take a while to send all documents to the client.

You'll see a much more noticeable difference for a large dataset by retrieving the data using a Meteor method.

At which point you've got it in a plain old javascript object anyway and so end up with the small performance gain of native object lookups as well.

Body:
My Meteor client receives data from the server and stores it in minimongo. This data is guaranteed not to change during their session, so I don't need Meteor's reactivity. The static data just happens to arrive by that route; let's just take that as a given.

The data looks like this:

{_id: 'abc...', val: {...}}

On the client, is it more efficient for me to look up values using:

val_I_need = Collection.findOne({id})

or to create a JavaScript object:

data = {}
Collection.find().fetch().map((x) => {data[x._id] = x.val})

and use it for look ups:

val_I_need = data[id]

Is there a tipping point, either in terms of the size of the data or the number of look ups, where the more efficient method changes, or outweighs the initial cost of building the object?


问:
Improve speed of complex postgres query in rails app
I have a view in my app that visualizes a lot of data, and in the backend the data is produced using this query:

DataPoint Load (20394.8ms)
SELECT communities.id as com,
       consumers.name as con,
       array_agg(timestamp ORDER BY data_points.timestamp asc) as tims,
       array_agg(consumption ORDER BY data_points.timestamp ASC) as cons
FROM "data_points"
     INNER JOIN "consumers" ON "consumers"."id" = "data_points"."consumer_id"
     INNER JOIN "communities_consumers" ON "communities_consumers"."consumer_id" = "consumers"."id"
     INNER JOIN "communities" ON "communities"."id" = "communities_consumers"."community_id"
     INNER JOIN "clusterings" ON "clusterings"."id" = "communities"."clustering_id"
WHERE ("data_points"."timestamp" BETWEEN $1 AND $2)
   AND "data_points"."interval_id" = $3
   AND "clusterings"."id" = 1
GROUP BY communities.id, consumers.id
[["timestamp", "2015-11-20 09:23:00"], ["timestamp", "2015-11-27 09:23:00"], ["interval_id", 2]]
```

<p>The query takes about 20 seconds to execute, which seems a bit excessive. </p>

<p>The code for generating the query is this:</p>

<pre class="lang-rb prettyprint-override"><code>res = {}
DataPoint.joins(consumer: {communities: :clustering} )
         .where('clusterings.id': self,
               timestamp: chart_cookies[:start_date] .. chart_cookies[:end_date],
               interval_id: chart_cookies[:interval_id])
         .group('communities.id')
         .group('consumers.id')
         .select('communities.id as com, consumers.name as con',
                'array_agg(timestamp ORDER BY data_points.timestamp asc) as tims',
                'array_agg(consumption ORDER BY data_points.timestamp ASC) as cons')
         .each do |d|
      res[d.com] ||= {}
      res[d.com][d.con] = d.tims.zip(d.cons)
      res[d.com]["aggregate"] ||= d.tims.map{|t| [t,0]}
      res[d.com]["aggregate"]  = res[d.com]["aggregate"].zip(d.cons).map{|(a,b),d| [a,(b+d)]}
end
res
</code>```

And the relevant database models are the following:

  create_table "data_points", force: :cascade do |t|
    t.bigint "consumer_id"
    t.bigint "interval_id"
    t.datetime "timestamp"
    t.float "consumption"
    t.float "flexibility"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
    t.index ["consumer_id"], name: "index_data_points_on_consumer_id"
    t.index ["interval_id"], name: "index_data_points_on_interval_id"
    t.index ["timestamp", "consumer_id", "interval_id"], name: "index_data_points_on_timestamp_and_consumer_id_and_interval_id", unique: true
    t.index ["timestamp"], name: "index_data_points_on_timestamp"
  end

  create_table "consumers", force: :cascade do |t|
    t.string "name"
    t.string "location"
    t.string "edms_id"
    t.bigint "building_type_id"
    t.bigint "connection_type_id"
    t.float "location_x"
    t.float "location_y"
    t.string "feeder_id"
    t.bigint "consumer_category_id"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
    t.index ["building_type_id"], name: "index_consumers_on_building_type_id"
    t.index ["connection_type_id"], name: "index_consumers_on_connection_type_id"
    t.index ["consumer_category_id"], name: "index_consumers_on_consumer_category_id"
  end

  create_table "communities_consumers", id: false, force: :cascade do |t|
    t.bigint "consumer_id", null: false
    t.bigint "community_id", null: false
    t.index ["community_id", "consumer_id"], name: "index_communities_consumers_on_community_id_and_consumer_id"
    t.index ["consumer_id", "community_id"], name: "index_communities_consumers_on_consumer_id_and_community_id"
  end

  create_table "communities", force: :cascade do |t|
    t.string "name"
    t.text "description"
    t.bigint "clustering_id"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
    t.index ["clustering_id"], name: "index_communities_on_clustering_id"
  end

  create_table "clusterings", force: :cascade do |t|
    t.string "name"
    t.text "description"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
  end
```

<p>How can I make the query execute faster? Is it possible to refactor the query to simplify it, or to add some extra index to the database schema so that it takes a shorter time?</p>

<p>Interestingly, a slightly simplified version of the query, which I use in another view, runs much faster, in only 1161.4ms for the first request and 41.6ms for the following requests:</p>

<pre class="lang-sql prettyprint-override"><code>DataPoint Load (1161.4ms)
SELECT consumers.name as con,
       array_agg(timestamp ORDER BY data_points.timestamp asc) as tims,
       array_agg(consumption ORDER BY data_points.timestamp ASC) as cons
FROM "data_points"
    INNER JOIN "consumers" ON "consumers"."id" = "data_points"."consumer_id"
    INNER JOIN "communities_consumers" ON "communities_consumers"."consumer_id" = "consumers"."id"
    INNER JOIN "communities" ON "communities"."id" = "communities_consumers"."community_id"
WHERE ("data_points"."timestamp" BETWEEN $1 AND $2)
   AND "data_points"."interval_id" = $3
   AND "communities"."id" = 100 GROUP BY communities.id, consumers.name
[["timestamp", "2015-11-20 09:23:00"], ["timestamp", "2015-11-27 09:23:00"], ["interval_id", 2]]
</code>```

Using command EXPLAIN (ANALYZE, BUFFERS) with query in dbconsole, I get the following output:

```<code>----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=12.31..7440.69 rows=246 width=57) (actual time=44.139..20474.015 rows=296 loops=1)
   Group Key: communities.id, consumers.id
   Buffers: shared hit=159692 read=6148105 written=209
   ->  Nested Loop  (cost=12.31..7434.54 rows=246 width=57) (actual time=20.944..20436.806 rows=49728 loops=1)
         Buffers: shared hit=159685 read=6148105 written=209
         ->  Nested Loop  (cost=11.88..49.30 rows=1 width=49) (actual time=0.102..6.374 rows=296 loops=1)
               Buffers: shared hit=988 read=208
               ->  Nested Loop  (cost=11.73..41.12 rows=1 width=57) (actual time=0.084..4.443 rows=296 loops=1)
                     Buffers: shared hit=396 read=208
                     ->  Merge Join  (cost=11.58..40.78 rows=1 width=24) (actual time=0.075..1.365 rows=296 loops=1)
                           Merge Cond: (communities_consumers.community_id = communities.id)
                           Buffers: shared hit=5 read=7
                           ->  Index Only Scan using index_communities_consumers_on_community_id_and_consumer_id on communities_consumers  (cost=0.27..28.71 rows=296 width=16) (actual time=0.039..0.446 rows=296 loops=1)
                                 Heap Fetches: 4
                                 Buffers: shared hit=1 read=6
                           ->  Sort  (cost=11.31..11.31 rows=3 width=16) (actual time=0.034..0.213 rows=247 loops=1)
                                 Sort Key: communities.id
                                 Sort Method: quicksort  Memory: 25kB
                                 Buffers: shared hit=4 read=1
                                 ->  Bitmap Heap Scan on communities  (cost=4.17..11.28 rows=3 width=16) (actual time=0.026..0.027 rows=6 loops=1)
                                       Recheck Cond: (clustering_id = 1)
                                       Heap Blocks: exact=1
                                       Buffers: shared hit=4 read=1
                                       ->  Bitmap Index Scan on index_communities_on_clustering_id  (cost=0.00..4.17 rows=3 width=0) (actual time=0.020..0.020 rows=8 loops=1)
                                             Index Cond: (clustering_id = 1)
                                             Buffers: shared hit=3 read=1
                     ->  Index Scan using consumers_pkey on consumers  (cost=0.15..0.33 rows=1 width=33) (actual time=0.007..0.008 rows=1 loops=296)
                           Index Cond: (id = communities_consumers.consumer_id)
                           Buffers: shared hit=391 read=201
               ->  Index Only Scan using clusterings_pkey on clusterings  (cost=0.15..8.17 rows=1 width=8) (actual time=0.004..0.005 rows=1 loops=296)
                     Index Cond: (id = 1)
                     Heap Fetches: 296
                     Buffers: shared hit=592
         ->  Index Scan using index_data_points_on_consumer_id on data_points  (cost=0.44..7383.44 rows=180 width=24) (actual time=56.128..68.995 rows=168 loops=296)
               Index Cond: (consumer_id = consumers.id)
               Filter: (("timestamp" >= '2015-11-20 09:23:00'::timestamp without time zone) AND ("timestamp" <= '2015-11-27 09:23:00'::timestamp without time zone) AND (interval_id = 2))
               Rows Removed by Filter: 76610
               Buffers: shared hit=158697 read=6147897 written=209
 Planning time: 1.811 ms
 Execution time: 20474.330 ms
(40 rows)
</code>```

The bullet gem returns the following warnings:

```<code>USE eager loading detected
  Community => [:communities_consumers]
  Add to your finder: :includes => [:communities_consumers]

USE eager loading detected
  Community => [:consumers]
  Add to your finder: :includes => [:consumers]
</code>```

After removing the join with the clusterings table, the new query plan is the following:

```<code>EXPLAIN for: SELECT communities.id as com, consumers.name as con, array_agg(timestamp ORDER BY data_points.timestamp asc) as tims, array_agg(consumption ORDER BY data_points.timestamp ASC) as cons FROM "data_points" INNER JOIN "consumers" ON "consumers"."id" = "data_points"."consumer_id" INNER JOIN "communities_consumers" ON "communities_consumers"."consumer_id" = "consumers"."id" INNER JOIN "communities" ON "communities"."id" = "communities_consumers"."community_id" WHERE ("data_points"."timestamp" BETWEEN $1 AND $2) AND "data_points"."interval_id" = $3 AND "communities"."clustering_id" = 1 GROUP BY communities.id, consumers.id [["timestamp", "2015-11-29 20:52:30.926247"], ["timestamp", "2015-12-06 20:52:30.926468"], ["interval_id", 2]]
                                                                                QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=10839.79..10846.42 rows=241 width=57)
   ->  Sort  (cost=10839.79..10840.39 rows=241 width=57)
         Sort Key: communities.id, consumers.id
         ->  Nested Loop  (cost=7643.11..10830.26 rows=241 width=57)
               ->  Nested Loop  (cost=11.47..22.79 rows=1 width=49)
                     ->  Hash Join  (cost=11.32..17.40 rows=1 width=16)
                           Hash Cond: (communities_consumers.community_id = communities.id)
                           ->  Seq Scan on communities_consumers  (cost=0.00..4.96 rows=296 width=16)
                           ->  Hash  (cost=11.28..11.28 rows=3 width=8)
                                 ->  Bitmap Heap Scan on communities  (cost=4.17..11.28 rows=3 width=8)
                                       Recheck Cond: (clustering_id = 1)
                                       ->  Bitmap Index Scan on index_communities_on_clustering_id  (cost=0.00..4.17 rows=3 width=0)
                                             Index Cond: (clustering_id = 1)
                     ->  Index Scan using consumers_pkey on consumers  (cost=0.15..5.38 rows=1 width=33)
                           Index Cond: (id = communities_consumers.consumer_id)
               ->  Bitmap Heap Scan on data_points  (cost=7631.64..10805.72 rows=174 width=24)
                     Recheck Cond: ((consumer_id = consumers.id) AND ("timestamp" >= '2015-11-29 20:52:30.926247'::timestamp without time zone) AND ("timestamp" <= '2015-12-06 20:52:30.926468'::timestamp without time zone))
                     Filter: (interval_id = 2::bigint)
                     ->  BitmapAnd  (cost=7631.64..7631.64 rows=861 width=0)
                           ->  Bitmap Index Scan on index_data_points_on_consumer_id  (cost=0.00..1589.92 rows=76778 width=0)
                                 Index Cond: (consumer_id = consumers.id)
                           ->  Bitmap Index Scan on index_data_points_on_timestamp  (cost=0.00..6028.58 rows=254814 width=0)
                                 Index Cond: (("timestamp" >= '2015-11-29 20:52:30.926247'::timestamp without time zone) AND ("timestamp" <= '2015-12-06 20:52:30.926468'::timestamp without time zone))
(23 rows)
</code>```

As requested in the comments, these are the query plans for the simplified query, with and without the restriction on communities.id

```<code> DataPoint Load (1563.3ms)  SELECT consumers.name as con, array_agg(timestamp ORDER BY data_points.timestamp asc) as tims, array_agg(consumption ORDER BY data_points.timestamp ASC) as cons FROM "data_points" INNER JOIN "consumers" ON "consumers"."id" = "data_points"."consumer_id" INNER JOIN "communities_consumers" ON "communities_consumers"."consumer_id" = "consumers"."id" INNER JOIN "communities" ON "communities"."id" = "communities_consumers"."community_id" WHERE ("data_points"."timestamp" BETWEEN $1 AND $2) AND "data_points"."interval_id" = $3 GROUP BY communities.id, consumers.name  [["timestamp", "2015-11-29 20:52:30.926000"], ["timestamp", "2015-12-06 20:52:30.926000"], ["interval_id", 2]]
EXPLAIN for: SELECT consumers.name as con, array_agg(timestamp ORDER BY data_points.timestamp asc) as tims, array_agg(consumption ORDER BY data_points.timestamp ASC) as cons FROM "data_points" INNER JOIN "consumers" ON "consumers"."id" = "data_points"."consumer_id" INNER JOIN "communities_consumers" ON "communities_consumers"."consumer_id" = "consumers"."id" INNER JOIN "communities" ON "communities"."id" = "communities_consumers"."community_id" WHERE ("data_points"."timestamp" BETWEEN $1 AND $2) AND "data_points"."interval_id" = $3 GROUP BY communities.id, consumers.name [["timestamp", "2015-11-29 20:52:30.926000"], ["timestamp", "2015-12-06 20:52:30.926000"], ["interval_id", 2]]
                                                                                QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=140992.34..142405.51 rows=51388 width=49)
   ->  Sort  (cost=140992.34..141120.81 rows=51388 width=49)
         Sort Key: communities.id, consumers.name
         ->  Hash Join  (cost=10135.44..135214.45 rows=51388 width=49)
               Hash Cond: (data_points.consumer_id = consumers.id)
               ->  Bitmap Heap Scan on data_points  (cost=10082.58..134455.00 rows=51388 width=24)
                     Recheck Cond: (("timestamp" >= '2015-11-29 20:52:30.926'::timestamp without time zone) AND ("timestamp" <= '2015-12-06 20:52:30.926'::timestamp without time zone) AND (interval_id = 2::bigint))
                     ->  Bitmap Index Scan on index_data_points_on_timestamp_and_consumer_id_and_interval_id  (cost=0.00..10069.74 rows=51388 width=0)
                           Index Cond: (("timestamp" >= '2015-11-29 20:52:30.926'::timestamp without time zone) AND ("timestamp" <= '2015-12-06 20:52:30.926'::timestamp without time zone) AND (interval_id = 2::bigint))
               ->  Hash  (cost=49.16..49.16 rows=296 width=49)
                     ->  Hash Join  (cost=33.06..49.16 rows=296 width=49)
                           Hash Cond: (communities_consumers.community_id = communities.id)
                           ->  Hash Join  (cost=8.66..20.69 rows=296 width=49)
                                 Hash Cond: (consumers.id = communities_consumers.consumer_id)
                                 ->  Seq Scan on consumers  (cost=0.00..7.96 rows=296 width=33)
                                 ->  Hash  (cost=4.96..4.96 rows=296 width=16)
                                       ->  Seq Scan on communities_consumers  (cost=0.00..4.96 rows=296 width=16)
                           ->  Hash  (cost=16.40..16.40 rows=640 width=8)
                                 ->  Seq Scan on communities  (cost=0.00..16.40 rows=640 width=8)
(19 rows)
</code>```

and

```<code>  DataPoint Load (1479.0ms)  SELECT consumers.name as con, array_agg(timestamp ORDER BY data_points.timestamp asc) as tims, array_agg(consumption ORDER BY data_points.timestamp ASC) as cons FROM "data_points" INNER JOIN "consumers" ON "consumers"."id" = "data_points"."consumer_id" INNER JOIN "communities_consumers" ON "communities_consumers"."consumer_id" = "consumers"."id" INNER JOIN "communities" ON "communities"."id" = "communities_consumers"."community_id" WHERE ("data_points"."timestamp" BETWEEN $1 AND $2) AND "data_points"."interval_id" = $3 GROUP BY communities.id, consumers.name  [["timestamp", "2015-11-29 20:52:30.926000"], ["timestamp", "2015-12-06 20:52:30.926000"], ["interval_id", 2]]
EXPLAIN for: SELECT consumers.name as con, array_agg(timestamp ORDER BY data_points.timestamp asc) as tims, array_agg(consumption ORDER BY data_points.timestamp ASC) as cons FROM "data_points" INNER JOIN "consumers" ON "consumers"."id" = "data_points"."consumer_id" INNER JOIN "communities_consumers" ON "communities_consumers"."consumer_id" = "consumers"."id" INNER JOIN "communities" ON "communities"."id" = "communities_consumers"."community_id" WHERE ("data_points"."timestamp" BETWEEN $1 AND $2) AND "data_points"."interval_id" = $3 GROUP BY communities.id, consumers.name [["timestamp", "2015-11-29 20:52:30.926000"], ["timestamp", "2015-12-06 20:52:30.926000"], ["interval_id", 2]]
                                                                                QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=140992.34..142405.51 rows=51388 width=49)
   ->  Sort  (cost=140992.34..141120.81 rows=51388 width=49)
         Sort Key: communities.id, consumers.name
         ->  Hash Join  (cost=10135.44..135214.45 rows=51388 width=49)
               Hash Cond: (data_points.consumer_id = consumers.id)
               ->  Bitmap Heap Scan on data_points  (cost=10082.58..134455.00 rows=51388 width=24)
                     Recheck Cond: (("timestamp" >= '2015-11-29 20:52:30.926'::timestamp without time zone) AND ("timestamp" <= '2015-12-06 20:52:30.926'::timestamp without time zone) AND (interval_id = 2::bigint))
                     ->  Bitmap Index Scan on index_data_points_on_timestamp_and_consumer_id_and_interval_id  (cost=0.00..10069.74 rows=51388 width=0)
                           Index Cond: (("timestamp" >= '2015-11-29 20:52:30.926'::timestamp without time zone) AND ("timestamp" <= '2015-12-06 20:52:30.926'::timestamp without time zone) AND (interval_id = 2::bigint))
               ->  Hash  (cost=49.16..49.16 rows=296 width=49)
                     ->  Hash Join  (cost=33.06..49.16 rows=296 width=49)
                           Hash Cond: (communities_consumers.community_id = communities.id)
                           ->  Hash Join  (cost=8.66..20.69 rows=296 width=49)
                                 Hash Cond: (consumers.id = communities_consumers.consumer_id)
                                 ->  Seq Scan on consumers  (cost=0.00..7.96 rows=296 width=33)
                                 ->  Hash  (cost=4.96..4.96 rows=296 width=16)
                                       ->  Seq Scan on communities_consumers  (cost=0.00..4.96 rows=296 width=16)
                           ->  Hash  (cost=16.40..16.40 rows=640 width=8)
                                 ->  Seq Scan on communities  (cost=0.00..16.40 rows=640 width=8)
(19 rows)
</code>```

答:
So here we have Postgres 9.3 and long query. Well Before query you have to ensure that you have optimal settings for you data base and suitable for your read and write percentage to disk, type of disk ssd or old hard, and you don't switch autovacuum, you check bloating for tables and indexes and you have good selectivity for indexes that are used for building optimal plans.

Check row types and size filled in row. Change type of row also cen reduce size of table and time.

So now you ensure in all this. Now lets think in way of how Postgres execute and how we can reduce time and efforts. ORM good for simple queries, but if you try to do complicated query you have to use execute by sql methods and keep in in Query Service Objects.

Write simpler queries as possible in sql Postgres also waste time for parse queries.

Check indexes on on all joins fields. Use explain analyze to check that now you have optimal scanning methods.

Next point. You try to do 4 joins! Postgres try to find optimal query plan in 4! times (4 factorial times!) let think to use subqueries or tables with predefined table for this selection.

Use separated query or function for 4 joins (try subqueries):

```<code>SELECT *
FROM "data_points" as predefined
INNER JOIN "consumers"
ON "consumers"."id" ="data_points"."consumer_id"
INNER JOIN "communities_consumers"
ON "communities_consumers"."consumer_id" = "consumers"."id"
INNER JOIN "communities"
ON "communities"."id" = "communities_consumers"."community_id"
INNER JOIN "clusterings"
ON "clusterings"."id" "communities"."clustering_id"

WHERE "data_points"."interval_id" = 2
AND "clusterings"."id" = 1
</code>```

2) Next (don't use variables just pass)

```<code>SELECT *
FROM predefined
WHERE "data_points"."timestamp"
BETWEEN "2015-11-20 09:23:00"
AND 2015-11-27 09:23:00
</code>```

3) You have 3 times of asking data_points for query, you need less:

```<code>array_agg(timestamp ORDER BY data_points.timestamp asc) as tims
array_agg(consumption ORDER BY data_points.timestamp ASC) as cons
WHERE ("data_points"."timestamp" BETWEEN $1 AND $2)
</code>```

You should remember long query it's not all about query, at about settings, ORM usage, sql, and how Postgres works with it all.

Body:
I have a view in my app that visualizes a lot of data, and in the backend the data is produced using this query:

DataPoint Load (20394.8ms)
SELECT communities.id as com,
       consumers.name as con,
       array_agg(timestamp ORDER BY data_points.timestamp asc) as tims,
       array_agg(consumption ORDER BY data_points.timestamp ASC) as cons
FROM "data_points"
     INNER JOIN "consumers" ON "consumers"."id" = "data_points"."consumer_id"
     INNER JOIN "communities_consumers" ON "communities_consumers"."consumer_id" = "consumers"."id"
     INNER JOIN "communities" ON "communities"."id" = "communities_consumers"."community_id"
     INNER JOIN "clusterings" ON "clusterings"."id" = "communities"."clustering_id"
WHERE ("data_points"."timestamp" BETWEEN $1 AND $2)
   AND "data_points"."interval_id" = $3
   AND "clusterings"."id" = 1
GROUP BY communities.id, consumers.id
[["timestamp", "2015-11-20 09:23:00"], ["timestamp", "2015-11-27 09:23:00"], ["interval_id", 2]]

The query takes about 20 seconds to execute, which seems a bit excessive.

The code for generating the query is this:

res = {}
DataPoint.joins(consumer: {communities: :clustering} )
         .where('clusterings.id': self,
               timestamp: chart_cookies[:start_date] .. chart_cookies[:end_date],
               interval_id: chart_cookies[:interval_id])
         .group('communities.id')
         .group('consumers.id')
         .select('communities.id as com, consumers.name as con',
                'array_agg(timestamp ORDER BY data_points.timestamp asc) as tims',
                'array_agg(consumption ORDER BY data_points.timestamp ASC) as cons')
         .each do |d|
      res[d.com] ||= {}
      res[d.com][d.con] = d.tims.zip(d.cons)
      res[d.com]["aggregate"] ||= d.tims.map{|t| [t,0]}
      res[d.com]["aggregate"]  = res[d.com]["aggregate"].zip(d.cons).map{|(a,b),d| [a,(b+d)]}
end
res

And the relevant database models are the following:

  create_table "data_points", force: :cascade do |t|
    t.bigint "consumer_id"
    t.bigint "interval_id"
    t.datetime "timestamp"
    t.float "consumption"
    t.float "flexibility"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
    t.index ["consumer_id"], name: "index_data_points_on_consumer_id"
    t.index ["interval_id"], name: "index_data_points_on_interval_id"
    t.index ["timestamp", "consumer_id", "interval_id"], name: "index_data_points_on_timestamp_and_consumer_id_and_interval_id", unique: true
    t.index ["timestamp"], name: "index_data_points_on_timestamp"
  end

  create_table "consumers", force: :cascade do |t|
    t.string "name"
    t.string "location"
    t.string "edms_id"
    t.bigint "building_type_id"
    t.bigint "connection_type_id"
    t.float "location_x"
    t.float "location_y"
    t.string "feeder_id"
    t.bigint "consumer_category_id"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
    t.index ["building_type_id"], name: "index_consumers_on_building_type_id"
    t.index ["connection_type_id"], name: "index_consumers_on_connection_type_id"
    t.index ["consumer_category_id"], name: "index_consumers_on_consumer_category_id"
  end

  create_table "communities_consumers", id: false, force: :cascade do |t|
    t.bigint "consumer_id", null: false
    t.bigint "community_id", null: false
    t.index ["community_id", "consumer_id"], name: "index_communities_consumers_on_community_id_and_consumer_id"
    t.index ["consumer_id", "community_id"], name: "index_communities_consumers_on_consumer_id_and_community_id"
  end

  create_table "communities", force: :cascade do |t|
    t.string "name"
    t.text "description"
    t.bigint "clustering_id"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
    t.index ["clustering_id"], name: "index_communities_on_clustering_id"
  end

  create_table "clusterings", force: :cascade do |t|
    t.string "name"
    t.text "description"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
  end

How can I make the query execute faster? Is it possible to refactor the query to simplify it, or to add some extra index to the database schema so that it takes a shorter time?

Interestingly, a slightly simplified version of the query, which I use in another view, runs much faster, in only 1161.4ms for the first request and 41.6ms for the following requests:

DataPoint Load (1161.4ms)
SELECT consumers.name as con,
       array_agg(timestamp ORDER BY data_points.timestamp asc) as tims,
       array_agg(consumption ORDER BY data_points.timestamp ASC) as cons
FROM "data_points"
    INNER JOIN "consumers" ON "consumers"."id" = "data_points"."consumer_id"
    INNER JOIN "communities_consumers" ON "communities_consumers"."consumer_id" = "consumers"."id"
    INNER JOIN "communities" ON "communities"."id" = "communities_consumers"."community_id"
WHERE ("data_points"."timestamp" BETWEEN $1 AND $2)
   AND "data_points"."interval_id" = $3
   AND "communities"."id" = 100 GROUP BY communities.id, consumers.name
[["timestamp", "2015-11-20 09:23:00"], ["timestamp", "2015-11-27 09:23:00"], ["interval_id", 2]]

Using command EXPLAIN (ANALYZE, BUFFERS) with query in dbconsole, I get the following output:

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=12.31..7440.69 rows=246 width=57) (actual time=44.139..20474.015 rows=296 loops=1)
   Group Key: communities.id, consumers.id
   Buffers: shared hit=159692 read=6148105 written=209
   ->  Nested Loop  (cost=12.31..7434.54 rows=246 width=57) (actual time=20.944..20436.806 rows=49728 loops=1)
         Buffers: shared hit=159685 read=6148105 written=209
         ->  Nested Loop  (cost=11.88..49.30 rows=1 width=49) (actual time=0.102..6.374 rows=296 loops=1)
               Buffers: shared hit=988 read=208
               ->  Nested Loop  (cost=11.73..41.12 rows=1 width=57) (actual time=0.084..4.443 rows=296 loops=1)
                     Buffers: shared hit=396 read=208
                     ->  Merge Join  (cost=11.58..40.78 rows=1 width=24) (actual time=0.075..1.365 rows=296 loops=1)
                           Merge Cond: (communities_consumers.community_id = communities.id)
                           Buffers: shared hit=5 read=7
                           ->  Index Only Scan using index_communities_consumers_on_community_id_and_consumer_id on communities_consumers  (cost=0.27..28.71 rows=296 width=16) (actual time=0.039..0.446 rows=296 loops=1)
                                 Heap Fetches: 4
                                 Buffers: shared hit=1 read=6
                           ->  Sort  (cost=11.31..11.31 rows=3 width=16) (actual time=0.034..0.213 rows=247 loops=1)
                                 Sort Key: communities.id
                                 Sort Method: quicksort  Memory: 25kB
                                 Buffers: shared hit=4 read=1
                                 ->  Bitmap Heap Scan on communities  (cost=4.17..11.28 rows=3 width=16) (actual time=0.026..0.027 rows=6 loops=1)
                                       Recheck Cond: (clustering_id = 1)
                                       Heap Blocks: exact=1
                                       Buffers: shared hit=4 read=1
                                       ->  Bitmap Index Scan on index_communities_on_clustering_id  (cost=0.00..4.17 rows=3 width=0) (actual time=0.020..0.020 rows=8 loops=1)
                                             Index Cond: (clustering_id = 1)
                                             Buffers: shared hit=3 read=1
                     ->  Index Scan using consumers_pkey on consumers  (cost=0.15..0.33 rows=1 width=33) (actual time=0.007..0.008 rows=1 loops=296)
                           Index Cond: (id = communities_consumers.consumer_id)
                           Buffers: shared hit=391 read=201
               ->  Index Only Scan using clusterings_pkey on clusterings  (cost=0.15..8.17 rows=1 width=8) (actual time=0.004..0.005 rows=1 loops=296)
                     Index Cond: (id = 1)
                     Heap Fetches: 296
                     Buffers: shared hit=592
         ->  Index Scan using index_data_points_on_consumer_id on data_points  (cost=0.44..7383.44 rows=180 width=24) (actual time=56.128..68.995 rows=168 loops=296)
               Index Cond: (consumer_id = consumers.id)
               Filter: (("timestamp" >= '2015-11-20 09:23:00'::timestamp without time zone) AND ("timestamp" <= '2015-11-27 09:23:00'::timestamp without time zone) AND (interval_id = 2))
               Rows Removed by Filter: 76610
               Buffers: shared hit=158697 read=6147897 written=209
 Planning time: 1.811 ms
 Execution time: 20474.330 ms
(40 rows)

The bullet gem returns the following warnings:

USE eager loading detected
  Community => [:communities_consumers]
  Add to your finder: :includes => [:communities_consumers]

USE eager loading detected
  Community => [:consumers]
  Add to your finder: :includes => [:consumers]

After removing the join with the clusterings table, the new query plan is the following:

EXPLAIN for: SELECT communities.id as com, consumers.name as con, array_agg(timestamp ORDER BY data_points.timestamp asc) as tims, array_agg(consumption ORDER BY data_points.timestamp ASC) as cons FROM "data_points" INNER JOIN "consumers" ON "consumers"."id" = "data_points"."consumer_id" INNER JOIN "communities_consumers" ON "communities_consumers"."consumer_id" = "consumers"."id" INNER JOIN "communities" ON "communities"."id" = "communities_consumers"."community_id" WHERE ("data_points"."timestamp" BETWEEN $1 AND $2) AND "data_points"."interval_id" = $3 AND "communities"."clustering_id" = 1 GROUP BY communities.id, consumers.id [["timestamp", "2015-11-29 20:52:30.926247"], ["timestamp", "2015-12-06 20:52:30.926468"], ["interval_id", 2]]
                                                                                QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=10839.79..10846.42 rows=241 width=57)
   ->  Sort  (cost=10839.79..10840.39 rows=241 width=57)
         Sort Key: communities.id, consumers.id
         ->  Nested Loop  (cost=7643.11..10830.26 rows=241 width=57)
               ->  Nested Loop  (cost=11.47..22.79 rows=1 width=49)
                     ->  Hash Join  (cost=11.32..17.40 rows=1 width=16)
                           Hash Cond: (communities_consumers.community_id = communities.id)
                           ->  Seq Scan on communities_consumers  (cost=0.00..4.96 rows=296 width=16)
                           ->  Hash  (cost=11.28..11.28 rows=3 width=8)
                                 ->  Bitmap Heap Scan on communities  (cost=4.17..11.28 rows=3 width=8)
                                       Recheck Cond: (clustering_id = 1)
                                       ->  Bitmap Index Scan on index_communities_on_clustering_id  (cost=0.00..4.17 rows=3 width=0)
                                             Index Cond: (clustering_id = 1)
                     ->  Index Scan using consumers_pkey on consumers  (cost=0.15..5.38 rows=1 width=33)
                           Index Cond: (id = communities_consumers.consumer_id)
               ->  Bitmap Heap Scan on data_points  (cost=7631.64..10805.72 rows=174 width=24)
                     Recheck Cond: ((consumer_id = consumers.id) AND ("timestamp" >= '2015-11-29 20:52:30.926247'::timestamp without time zone) AND ("timestamp" <= '2015-12-06 20:52:30.926468'::timestamp without time zone))
                     Filter: (interval_id = 2::bigint)
                     ->  BitmapAnd  (cost=7631.64..7631.64 rows=861 width=0)
                           ->  Bitmap Index Scan on index_data_points_on_consumer_id  (cost=0.00..1589.92 rows=76778 width=0)
                                 Index Cond: (consumer_id = consumers.id)
                           ->  Bitmap Index Scan on index_data_points_on_timestamp  (cost=0.00..6028.58 rows=254814 width=0)
                                 Index Cond: (("timestamp" >= '2015-11-29 20:52:30.926247'::timestamp without time zone) AND ("timestamp" <= '2015-12-06 20:52:30.926468'::timestamp without time zone))
(23 rows)

As requested in the comments, these are the query plans for the simplified query, with and without the restriction on communities.id

 DataPoint Load (1563.3ms)  SELECT consumers.name as con, array_agg(timestamp ORDER BY data_points.timestamp asc) as tims, array_agg(consumption ORDER BY data_points.timestamp ASC) as cons FROM "data_points" INNER JOIN "consumers" ON "consumers"."id" = "data_points"."consumer_id" INNER JOIN "communities_consumers" ON "communities_consumers"."consumer_id" = "consumers"."id" INNER JOIN "communities" ON "communities"."id" = "communities_consumers"."community_id" WHERE ("data_points"."timestamp" BETWEEN $1 AND $2) AND "data_points"."interval_id" = $3 GROUP BY communities.id, consumers.name  [["timestamp", "2015-11-29 20:52:30.926000"], ["timestamp", "2015-12-06 20:52:30.926000"], ["interval_id", 2]]
EXPLAIN for: SELECT consumers.name as con, array_agg(timestamp ORDER BY data_points.timestamp asc) as tims, array_agg(consumption ORDER BY data_points.timestamp ASC) as cons FROM "data_points" INNER JOIN "consumers" ON "consumers"."id" = "data_points"."consumer_id" INNER JOIN "communities_consumers" ON "communities_consumers"."consumer_id" = "consumers"."id" INNER JOIN "communities" ON "communities"."id" = "communities_consumers"."community_id" WHERE ("data_points"."timestamp" BETWEEN $1 AND $2) AND "data_points"."interval_id" = $3 GROUP BY communities.id, consumers.name [["timestamp", "2015-11-29 20:52:30.926000"], ["timestamp", "2015-12-06 20:52:30.926000"], ["interval_id", 2]]
                                                                                QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=140992.34..142405.51 rows=51388 width=49)
   ->  Sort  (cost=140992.34..141120.81 rows=51388 width=49)
         Sort Key: communities.id, consumers.name
         ->  Hash Join  (cost=10135.44..135214.45 rows=51388 width=49)
               Hash Cond: (data_points.consumer_id = consumers.id)
               ->  Bitmap Heap Scan on data_points  (cost=10082.58..134455.00 rows=51388 width=24)
                     Recheck Cond: (("timestamp" >= '2015-11-29 20:52:30.926'::timestamp without time zone) AND ("timestamp" <= '2015-12-06 20:52:30.926'::timestamp without time zone) AND (interval_id = 2::bigint))
                     ->  Bitmap Index Scan on index_data_points_on_timestamp_and_consumer_id_and_interval_id  (cost=0.00..10069.74 rows=51388 width=0)
                           Index Cond: (("timestamp" >= '2015-11-29 20:52:30.926'::timestamp without time zone) AND ("timestamp" <= '2015-12-06 20:52:30.926'::timestamp without time zone) AND (interval_id = 2::bigint))
               ->  Hash  (cost=49.16..49.16 rows=296 width=49)
                     ->  Hash Join  (cost=33.06..49.16 rows=296 width=49)
                           Hash Cond: (communities_consumers.community_id = communities.id)
                           ->  Hash Join  (cost=8.66..20.69 rows=296 width=49)
                                 Hash Cond: (consumers.id = communities_consumers.consumer_id)
                                 ->  Seq Scan on consumers  (cost=0.00..7.96 rows=296 width=33)
                                 ->  Hash  (cost=4.96..4.96 rows=296 width=16)
                                       ->  Seq Scan on communities_consumers  (cost=0.00..4.96 rows=296 width=16)
                           ->  Hash  (cost=16.40..16.40 rows=640 width=8)
                                 ->  Seq Scan on communities  (cost=0.00..16.40 rows=640 width=8)
(19 rows)

and

  DataPoint Load (1479.0ms)  SELECT consumers.name as con, array_agg(timestamp ORDER BY data_points.timestamp asc) as tims, array_agg(consumption ORDER BY data_points.timestamp ASC) as cons FROM "data_points" INNER JOIN "consumers" ON "consumers"."id" = "data_points"."consumer_id" INNER JOIN "communities_consumers" ON "communities_consumers"."consumer_id" = "consumers"."id" INNER JOIN "communities" ON "communities"."id" = "communities_consumers"."community_id" WHERE ("data_points"."timestamp" BETWEEN $1 AND $2) AND "data_points"."interval_id" = $3 GROUP BY communities.id, consumers.name  [["timestamp", "2015-11-29 20:52:30.926000"], ["timestamp", "2015-12-06 20:52:30.926000"], ["interval_id", 2]]
EXPLAIN for: SELECT consumers.name as con, array_agg(timestamp ORDER BY data_points.timestamp asc) as tims, array_agg(consumption ORDER BY data_points.timestamp ASC) as cons FROM "data_points" INNER JOIN "consumers" ON "consumers"."id" = "data_points"."consumer_id" INNER JOIN "communities_consumers" ON "communities_consumers"."consumer_id" = "consumers"."id" INNER JOIN "communities" ON "communities"."id" = "communities_consumers"."community_id" WHERE ("data_points"."timestamp" BETWEEN $1 AND $2) AND "data_points"."interval_id" = $3 GROUP BY communities.id, consumers.name [["timestamp", "2015-11-29 20:52:30.926000"], ["timestamp", "2015-12-06 20:52:30.926000"], ["interval_id", 2]]
                                                                                QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=140992.34..142405.51 rows=51388 width=49)
   ->  Sort  (cost=140992.34..141120.81 rows=51388 width=49)
         Sort Key: communities.id, consumers.name
         ->  Hash Join  (cost=10135.44..135214.45 rows=51388 width=49)
               Hash Cond: (data_points.consumer_id = consumers.id)
               ->  Bitmap Heap Scan on data_points  (cost=10082.58..134455.00 rows=51388 width=24)
                     Recheck Cond: (("timestamp" >= '2015-11-29 20:52:30.926'::timestamp without time zone) AND ("timestamp" <= '2015-12-06 20:52:30.926'::timestamp without time zone) AND (interval_id = 2::bigint))
                     ->  Bitmap Index Scan on index_data_points_on_timestamp_and_consumer_id_and_interval_id  (cost=0.00..10069.74 rows=51388 width=0)
                           Index Cond: (("timestamp" >= '2015-11-29 20:52:30.926'::timestamp without time zone) AND ("timestamp" <= '2015-12-06 20:52:30.926'::timestamp without time zone) AND (interval_id = 2::bigint))
               ->  Hash  (cost=49.16..49.16 rows=296 width=49)
                     ->  Hash Join  (cost=33.06..49.16 rows=296 width=49)
                           Hash Cond: (communities_consumers.community_id = communities.id)
                           ->  Hash Join  (cost=8.66..20.69 rows=296 width=49)
                                 Hash Cond: (consumers.id = communities_consumers.consumer_id)
                                 ->  Seq Scan on consumers  (cost=0.00..7.96 rows=296 width=33)
                                 ->  Hash  (cost=4.96..4.96 rows=296 width=16)
                                       ->  Seq Scan on communities_consumers  (cost=0.00..4.96 rows=296 width=16)
                           ->  Hash  (cost=16.40..16.40 rows=640 width=8)
                                 ->  Seq Scan on communities  (cost=0.00..16.40 rows=640 width=8)
(19 rows)

Body:
So here we have Postgres 9.3 and long query. Well Before query you have to ensure that you have optimal settings for you data base and suitable for your read and write percentage to disk, type of disk ssd or old hard, and you don't switch autovacuum, you check bloating for tables and indexes and you have good selectivity for indexes that are used for building optimal plans.

Check row types and size filled in row. Change type of row also cen reduce size of table and time.

So now you ensure in all this. Now lets think in way of how Postgres execute and how we can reduce time and efforts. ORM good for simple queries, but if you try to do complicated query you have to use execute by sql methods and keep in in Query Service Objects.

Write simpler queries as possible in sql Postgres also waste time for parse queries.

Check indexes on on all joins fields. Use explain analyze to check that now you have optimal scanning methods.

Next point. You try to do 4 joins! Postgres try to find optimal query plan in 4! times (4 factorial times!) let think to use subqueries or tables with predefined table for this selection.

Use separated query or function for 4 joins (try subqueries):

SELECT *
FROM "data_points" as predefined
INNER JOIN "consumers"
ON "consumers"."id" ="data_points"."consumer_id"
INNER JOIN "communities_consumers"
ON "communities_consumers"."consumer_id" = "consumers"."id"
INNER JOIN "communities"
ON "communities"."id" = "communities_consumers"."community_id"
INNER JOIN "clusterings"
ON "clusterings"."id" "communities"."clustering_id"

WHERE "data_points"."interval_id" = 2
AND "clusterings"."id" = 1

2) Next (don't use variables just pass)

SELECT *
FROM predefined
WHERE "data_points"."timestamp"
BETWEEN "2015-11-20 09:23:00"
AND 2015-11-27 09:23:00

3) You have 3 times of asking data_points for query, you need less:

array_agg(timestamp ORDER BY data_points.timestamp asc) as tims
array_agg(consumption ORDER BY data_points.timestamp ASC) as cons
WHERE ("data_points"."timestamp" BETWEEN $1 AND $2)

You should remember long query it's not all about query, at about settings, ORM usage, sql, and how Postgres works with it all.


问:
change clip path smoothly on scroll
```<code><script>
$("flexbox.center").scroll(changeClass);

function changeClass() {
    if($("#coverphoto").hasClass('.coverphoto')){
        $("#coverphoto").removeClass("coverphoto").addClass("newshape");
    }
    else{
        $("#coverphoto").removeClass("newshape").addClass("coverphoto");
    }
}
 </script>

clip-path: polygon(0% 0%, 100% 0%, 100% 38.5%, 28.5% 65%, 0 65%);
transition: 0.5s ease-in-out;
-----
clip-path: polygon(0% 0%, 100% 0%, 100% 53%, 41% 53%, 0 53%);
transition: 0.5s ease-in-out;
</code>```

Hello,

my goal is to smoothly change the shape of a clipped image on scroll.
My approach was to simply switch between the two css properties on scroll but this does not work. (By the way this works perfectly with :hover on the initial css property)

Any hints are highly appreciated and would mean a lot to me!

答:
You have error in your code, the hasClass() function doesn't take a selector but only the class name :

function changeClass() {
  if ($("#cover").hasClass('coverphoto')) {
    $("#cover").removeClass("coverphoto").addClass("newshape");
  } else {
    $("#cover").removeClass("newshape").addClass("coverphoto");
  }
}```
<pre class="snippet-code-css lang-css prettyprint-override"><code>.coverphoto {
  clip-path: polygon(0% 0%, 100% 0%, 100% 38.5%, 28.5% 65%, 0 65%);
  transition: 0.5s ease-in-out;
}

.newshape {
  clip-path: polygon(0% 0%, 100% 0%, 100% 53%, 41% 53%, 0 53%);
  transition: 0.5s ease-in-out;
}</code>```

<button onClick="changeClass()">change</button>```
</div>
</div>
</p>

<p>By the way you can optimize your logic by simply using <code>toggleClass()</code> :</p>

<p><div class="snippet" data-lang="js" data-hide="false" data-console="true" data-babel="false">
<div class="snippet-code">
<pre class="snippet-code-js lang-js prettyprint-override"><code>function changeClass() {
  $("#cover").toggleClass("newshape");
}</code>```

.coverphoto {
  clip-path: polygon(0% 0%, 100% 0%, 100% 38.5%, 28.5% 65%, 0 65%);
  transition: 0.5s ease-in-out;
}

.newshape {
  clip-path: polygon(0% 0%, 100% 0%, 100% 53%, 41% 53%, 0 53%);
}```
<pre class="snippet-code-html lang-html prettyprint-override"><code><script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script>
<div id="cover" class="coverphoto">
  <img src="https://lorempixel.com/400/150/">
</div>
<button onClick="changeClass()">change</button></code>```

Here is a trigger on scroll event:

$(document).scroll(function() {
  if ($(window).scrollTop() <= 150) {
    $("#cover").removeClass("newshape");
  } else {
    $("#cover").addClass("newshape");
  }
});```
<pre class="snippet-code-css lang-css prettyprint-override"><code>body {
  height: 150vh;
}

.coverphoto {
  position: fixed;
  clip-path: polygon(0% 0%, 100% 0%, 100% 38.5%, 28.5% 65%, 0 65%);
  transition: 0.5s ease-in-out;
}

.newshape {
  clip-path: polygon(0% 0%, 100% 0%, 100% 53%, 41% 53%, 0 53%);
}</code>```

```

Title: change clip path smoothly on scroll

Body:
clip-path: polygon(0% 0%, 100% 0%, 100% 38.5%, 28.5% 65%, 0 65%);
transition: 0.5s ease-in-out;
-----
clip-path: polygon(0% 0%, 100% 0%, 100% 53%, 41% 53%, 0 53%);
transition: 0.5s ease-in-out;

Hello,

my goal is to smoothly change the shape of a clipped image on scroll.
My approach was to simply switch between the two css properties on scroll but this does not work. (By the way this works perfectly with :hover on the initial css property)

Any hints are highly appreciated and would mean a lot to me!

Body:
You have error in your code, the hasClass() function doesn't take a selector but only the class name :

function changeClass() {
  if ($("#cover").hasClass('coverphoto')) {
    $("#cover").removeClass("coverphoto").addClass("newshape");
  } else {
    $("#cover").removeClass("newshape").addClass("coverphoto");
  }
}

.coverphoto {
  clip-path: polygon(0% 0%, 100% 0%, 100% 38.5%, 28.5% 65%, 0 65%);
  transition: 0.5s ease-in-out;
}

.newshape {
  clip-path: polygon(0% 0%, 100% 0%, 100% 53%, 41% 53%, 0 53%);
  transition: 0.5s ease-in-out;
}

<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script>
<div id="cover" class="coverphoto">
  <img src="https://lorempixel.com/400/150/">
</div>
<button onClick="changeClass()">change</button>

By the way you can optimize your logic by simply using toggleClass() :

function changeClass() {
  $("#cover").toggleClass("newshape");
}

.coverphoto {
  clip-path: polygon(0% 0%, 100% 0%, 100% 38.5%, 28.5% 65%, 0 65%);
  transition: 0.5s ease-in-out;
}

.newshape {
  clip-path: polygon(0% 0%, 100% 0%, 100% 53%, 41% 53%, 0 53%);
}

<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script>
<div id="cover" class="coverphoto">
  <img src="https://lorempixel.com/400/150/">
</div>
<button onClick="changeClass()">change</button>

Here is a trigger on scroll event:

$(document).scroll(function() {
  if ($(window).scrollTop() <= 150) {
    $("#cover").removeClass("newshape");
  } else {
    $("#cover").addClass("newshape");
  }
});

body {
  height: 150vh;
}

.coverphoto {
  position: fixed;
  clip-path: polygon(0% 0%, 100% 0%, 100% 38.5%, 28.5% 65%, 0 65%);
  transition: 0.5s ease-in-out;
}

.newshape {
  clip-path: polygon(0% 0%, 100% 0%, 100% 53%, 41% 53%, 0 53%);
}

<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script>
<div id="cover" class="coverphoto">
  <img src="https://lorempixel.com/400/150/">
</div>


问:
how do you organize your programming work
I'm a newbie in the field, but in the near future I have to develop an application for a friend(I've already did some work of the app and the friend is happy).

I assume that I need 3 places to store my work, but I'm not sure if this is the best approach. I need your advice, opinion, link,book, blog about this subject.

I plan to have:

a place where I develop the application

a place where I keep a back-up of the application

a place with the application ready for use

I'll use git in the development stage, but for the later I don't know what tools to use, or which are the good practices. Can you give me an advice?

PS: at this moment I'm using cakephp to build some webapps, but I play with C++ from time to time too.

答:
Most good advice has already been given:

Use source control / revision control. And backups. Really, really do this.

have separate instances for development, testing, integration/staging and production. If you lack the hardware, virtualize - there are some good commercial solutions, but even Virtualbox (for free) will often do the job.

In addition, I also recommend

Make backups of your stable instances at milestones. When it works, keep a copy.

Use an Issue Tracker like JIRA, Bugzilla or similar. At the very least, have a text file with a ToDo list and a good grep.

Place marks in your code like @TODO and @FIXME wherever applicable; most IDEs I know will find these automatically and present you a list of those. This may or may not integrate with the tracker.

I find it important to keep track of what's left to do and how important it is, differentiate between bugs, improvements, necessary tasks and nice-to-have items and so on. It's easy to get Lost In Development and forget stuff - programming requires you both to dive deep into the inner workings of code, and to keep the overall structure in mind. You'll need something to keep your mind free from all those "and I'll have to do this as well" things. Sticky yellow papers don't work that well, nor do writing pads, since with more than, say, 50 items, paper makes it hard to keep track.

There is software for that. Use it.

ContentLicense: CC BY-SA 2.5


问:
Parent click event not firing when recyclerview clicked
I have a RecyclerView which is in a CardView that has a couple of TextViews.  The CardView has a OnClickListener set and is fired off when clicking on the TextViews, but does not fire when clicking on the RecyclerView.

Here is what the CardView looks like:

```<code><?xml version="1.0" encoding="utf-8"?>
<android.support.v7.widget.CardView
    xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:card_view="http://schemas.android.com/apk/res-auto"
    android:id="@+id/card_view"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:layout_margin="5dp"
    card_view:cardCornerRadius="4dp"
    card_view:cardElevation="5dp">

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="match_parent"
        android:orientation="vertical"
        android:weightSum="100"
        android:minWidth="100dp">

        <TextView
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:textAppearance="?android:attr/textAppearanceLarge"
            android:id="@+id/text1"
            android:textColor="@color/abc_primary_text_material_light"
            android:layout_weight="1"
            android:layout_gravity="center_horizontal" />

        <android.support.v7.widget.RecyclerView
            android:id="@+id/recyclerView"
            android:layout_width="match_parent"
            android:layout_height="0dp"
            android:listSelector="@color/highlighted_text_material_light"
            android:layout_weight="98" />

        <View
            android:layout_width="match_parent"
            android:layout_height="1dp"
            android:background="@android:color/black" />

        <RelativeLayout
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:id="@+id/relativeSummary"
            android:orientation="horizontal"
            android:layout_weight="1">

            <TextView
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:textAppearance="?android:attr/textAppearanceLarge"
                android:id="@+id/text2"
                android:textAlignment="viewEnd"
                android:textColor="@color/abc_secondary_text_material_light"
                android:layout_marginLeft="5dp"
                android:layout_marginRight="5dp"
                android:gravity="start"
                android:singleLine="true"
                android:layout_alignParentLeft="true" />

            <TextView
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:textAppearance="?android:attr/textAppearanceLarge"
                android:id="@+id/text3"
                android:textColor="@color/abc_primary_text_material_light"
                android:layout_marginRight="5dp"
                android:layout_marginLeft="5dp"
                android:gravity="end"
                android:singleLine="true"
                android:layout_alignParentRight="true"
                android:layout_toRightOf="@+id/text2" />

        </RelativeLayout>
    </LinearLayout>
</android.support.v7.widget.CardView>
</code>```

I do not need a click listener on this RecyclerView and really only need the parent view's click event to fire when the RecyclerView is clicked (The same goes for the OnLongClick event).  I also need the RecyclerView to scroll.  Is the RecyclerView some how eating the click event and not passing it up to the parent?

答:
In my case, I had a CardView with a couple of buttons and a RecyclerView. With the solutions of ywwynm and Daryl the problem was that the CardView would intercept the events from all of its children views, including the buttons. But what I wanted was for the CardView to intercept the touch events of the RecyclerView only. My solution was the following:</p>

```<code>public class UntouchableRecyclerView extends RecyclerView {

    public UntouchableRecyclerView(Context context) {
        super(context);
    }

    public UntouchableRecyclerView(Context context, AttributeSet attrs) {
        super(context, attrs);
    }

    public UntouchableRecyclerView(Context context, AttributeSet attrs, int defStyleAttr) {
        super(context, attrs, defStyleAttr);
    }

    @Override
    public boolean onTouchEvent(MotionEvent e) {
        return false;
    }
}
</code>```

Body:
I have a RecyclerView which is in a CardView that has a couple of TextViews.  The CardView has a OnClickListener set and is fired off when clicking on the TextViews, but does not fire when clicking on the RecyclerView.

Here is what the CardView looks like:

<?xml version="1.0" encoding="utf-8"?>
<android.support.v7.widget.CardView
    xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:card_view="http://schemas.android.com/apk/res-auto"
    android:id="@+id/card_view"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:layout_margin="5dp"
    card_view:cardCornerRadius="4dp"
    card_view:cardElevation="5dp">

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="match_parent"
        android:orientation="vertical"
        android:weightSum="100"
        android:minWidth="100dp">

        <TextView
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:textAppearance="?android:attr/textAppearanceLarge"
            android:id="@+id/text1"
            android:textColor="@color/abc_primary_text_material_light"
            android:layout_weight="1"
            android:layout_gravity="center_horizontal" />

        <android.support.v7.widget.RecyclerView
            android:id="@+id/recyclerView"
            android:layout_width="match_parent"
            android:layout_height="0dp"
            android:listSelector="@color/highlighted_text_material_light"
            android:layout_weight="98" />

        <View
            android:layout_width="match_parent"
            android:layout_height="1dp"
            android:background="@android:color/black" />

        <RelativeLayout
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:id="@+id/relativeSummary"
            android:orientation="horizontal"
            android:layout_weight="1">

            <TextView
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:textAppearance="?android:attr/textAppearanceLarge"
                android:id="@+id/text2"
                android:textAlignment="viewEnd"
                android:textColor="@color/abc_secondary_text_material_light"
                android:layout_marginLeft="5dp"
                android:layout_marginRight="5dp"
                android:gravity="start"
                android:singleLine="true"
                android:layout_alignParentLeft="true" />

            <TextView
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:textAppearance="?android:attr/textAppearanceLarge"
                android:id="@+id/text3"
                android:textColor="@color/abc_primary_text_material_light"
                android:layout_marginRight="5dp"
                android:layout_marginLeft="5dp"
                android:gravity="end"
                android:singleLine="true"
                android:layout_alignParentRight="true"
                android:layout_toRightOf="@+id/text2" />

        </RelativeLayout>
    </LinearLayout>
</android.support.v7.widget.CardView>

I do not need a click listener on this RecyclerView and really only need the parent view's click event to fire when the RecyclerView is clicked (The same goes for the OnLongClick event).  I also need the RecyclerView to scroll.  Is the RecyclerView some how eating the click event and not passing it up to the parent?

Body:
In my case, I had a CardView with a couple of buttons and a RecyclerView. With the solutions of ywwynm and Daryl the problem was that the CardView would intercept the events from all of its children views, including the buttons. But what I wanted was for the CardView to intercept the touch events of the RecyclerView only. My solution was the following:

public class UntouchableRecyclerView extends RecyclerView {

    public UntouchableRecyclerView(Context context) {
        super(context);
    }

    public UntouchableRecyclerView(Context context, AttributeSet attrs) {
        super(context, attrs);
    }

    public UntouchableRecyclerView(Context context, AttributeSet attrs, int defStyleAttr) {
        super(context, attrs, defStyleAttr);
    }

    @Override
    public boolean onTouchEvent(MotionEvent e) {
        return false;
    }
}

ContentLicense: CC BY-SA 4.0


问:
how to identify the questions in the email body
I am new to the deep learning, I am solving the problem below:

I want to identify the questions present in the email body and give the text/answers suggestions for the questions present in the email.

I felt this is a question and auto answer problem, will seq2seq model will work for this type of problems?

if seq2seq will work how will I feed the data, please suggest any useful link.

答:
I think you can use regex and hand check to create a labeled dataset. Some ideas from the top of my mind:

Sentence ending with question mark "?"

Sentence starting interrogative adverbs (why, how, which ..)


问:
Choose folder or folder directory inside shiny app
I have a problem using shiny. I want to choose the folder where all the files I want to use in my app are saved either 1) by setting the working directory to that folderpath or 2) by uploading all csv data inside this folder to my app for further processing. for 1) I found the shinyFiles package but it is very very slow -not due to my PC- as well as giving me the error:

```<code>Warning: Error in dir.create: invalid 'path' argument
Stack trace (innermost first):
    59: dir.create
    58: dirCreate
    57: observerFunc
     2: runApp
     1: shinyFilesExample
</code>```

when I selected a folder and the create folder button becomes clickable and I am putting a name of the new folder into it and clicking on the "+" beneath that panel. Anybody knows why? Despite that this method works but is very very slow. code below:

```<code>library(shiny)
library(shinyFiles)

ui<-fluidPage(sidebarLayout(

  sidebarPanel(
    shinyDirButton("dir", "Chose directory", "Upload")
  ),

  mainPanel(
    h4("output$dir"),
    verbatimTextOutput("dir"), br()

  )

))

server <- function(input,output,session){

  # dir
  shinyDirChoose(input, 'dir', roots = getVolumes())
  dir <- reactive(input$dir)
  output$dir <- renderPrint(dir())

}
shinyApp(ui = ui, server = server)
</code>```

Is there another option? Maybe to upload all csv data via the fileInput function? Or another way? It should not work only locally but on a server so choose.dir might be not the right way.
Many thanks

答:
The funktion getwd() gets your current working directory.

```<code>server <- function(input,output,session){

  # dir
  shinyDirChoose(input, 'dir', roots = c(name=getwd()))
  dir <- reactive(input$dir)
  output$dir <- renderPrint(dir())
}
</code>```

Body:
I have a problem using shiny. I want to choose the folder where all the files I want to use in my app are saved either 1) by setting the working directory to that folderpath or 2) by uploading all csv data inside this folder to my app for further processing. for 1) I found the shinyFiles package but it is very very slow -not due to my PC- as well as giving me the error:

Warning: Error in dir.create: invalid 'path' argument
Stack trace (innermost first):
    59: dir.create
    58: dirCreate
    57: observerFunc
     2: runApp
     1: shinyFilesExample

when I selected a folder and the create folder button becomes clickable and I am putting a name of the new folder into it and clicking on the "+" beneath that panel. Anybody knows why? Despite that this method works but is very very slow. code below:

library(shiny)
library(shinyFiles)

ui<-fluidPage(sidebarLayout(

  sidebarPanel(
    shinyDirButton("dir", "Chose directory", "Upload")
  ),

  mainPanel(
    h4("output$dir"),
    verbatimTextOutput("dir"), br()

  )

))

server <- function(input,output,session){

  # dir
  shinyDirChoose(input, 'dir', roots = getVolumes())
  dir <- reactive(input$dir)
  output$dir <- renderPrint(dir())

}
shinyApp(ui = ui, server = server)

Is there another option? Maybe to upload all csv data via the fileInput function? Or another way? It should not work only locally but on a server so choose.dir might be not the right way.
Many thanks

Body:
The funktion getwd() gets your current working directory.

server <- function(input,output,session){

  # dir
  shinyDirChoose(input, 'dir', roots = c(name=getwd()))
  dir <- reactive(input$dir)
  output$dir <- renderPrint(dir())
}


问:
Creating a range of dates in Python
I want to create a list of dates, starting with today, and going back an arbitrary number of days, say, in my example 100 days. Is there a better way to do it than this?</p>

```<code>import datetime

a = datetime.datetime.today()
numdays = 100
dateList = []
for x in range (0, numdays):
    dateList.append(a - datetime.timedelta(days = x))
print dateList
</code>```

答:
Based on answers I wrote for myself this:

```<code>import datetime;
print [(datetime.date.today() - datetime.timedelta(days=x)).strftime('%Y-%m-%d') for x in range(-5, 0)]
</code>```

Output:

```<code>['2017-12-11', '2017-12-10', '2017-12-09', '2017-12-08', '2017-12-07']
</code>```

The difference is that I get the 'date' object, not the 'datetime.datetime' one.

Title: Creating a range of dates in Python

Body:
I want to create a list of dates, starting with today, and going back an arbitrary number of days, say, in my example 100 days. Is there a better way to do it than this?

import datetime

a = datetime.datetime.today()
numdays = 100
dateList = []
for x in range (0, numdays):
    dateList.append(a - datetime.timedelta(days = x))
print dateList

Body:
Based on answers I wrote for myself this:

import datetime;
print [(datetime.date.today() - datetime.timedelta(days=x)).strftime('%Y-%m-%d') for x in range(-5, 0)]

Output:

['2017-12-11', '2017-12-10', '2017-12-09', '2017-12-08', '2017-12-07']

The difference is that I get the 'date' object, not the 'datetime.datetime' one.


问:
NavigationListItem & Sort
I tried to sort a NavigationListItem on a field which is different from the keyfield within the onInit method of the view controller w/o any success.

Note, there is no orderby implementation on the server side.

My xml view which doesn't implement any sorter since orderby isn't implemented: https://openui5.hana.ondemand.com/#/api/sap.tnt.NavigationListItem

```<code> <tnt:NavigationList id="navigationList">
    <tnt:NavigationListItem icon="sap-icon://"
                            expanded="{expanded}"
                            key="{MY_KEY}"
                            items="{/MY_PATH}">
       <tnt:NavigationListItem text="{MY_CONTENT}" key="{MY_KEY}"></tnt:NavigationListItem>
    </tnt:NavigationListItem>
  </tnt:NavigationList>
</code>```

Here is an attempt to add a sorter that presently doesn't work. First, because return items correspond to the first level of NavigationListItem and secondly probably because of a wrong approach.

```<code>        var oNavigationList = this.getView().byId('navigationList');
        var aSorter = [];
        aSorter[0] = new sap.ui.model.Sorter('MY_FIELD', true);
        var oItems = oNavigationList.getItems();
        oItems.sort("items", {path: "/MY_PATH", sorter: aSorter});
</code>```

Could you please advice on the appropriate approach?

答:
Since your List is bound to ODATA Model, all the sorting will happen at server side. Every time you say, sort, a new request is sent to server. Now, as you said, the server is not supporting orderby, I suggest the following approach :

Do oDataModel.read and store the data in a JSON Model. oData Read API

Apply the sorting in XML using below code:

```<code>    <tnt:NavigationList id="navigationList">
        <tnt:NavigationListItem icon="sap-icon://"
            expanded="{expanded}" key="{MY_KEY}"
            items="{
                        path : '/MY_PATH',
                        sorter: {
                            path: 'MY_CONTENT',
                            descending: false,
                            group: false
                        },
                    }">
            <tnt:NavigationListItem text="{MY_CONTENT}"
                key="{MY_KEY}"></tnt:NavigationListItem>
        </tnt:NavigationListItem>
    </tnt:NavigationList>
</code>```

Let me know if you need more information.

Title: NavigationListItem & Sort

Body:
I tried to sort a NavigationListItem on a field which is different from the keyfield within the onInit method of the view controller w/o any success.

Note, there is no orderby implementation on the server side.

My xml view which doesn't implement any sorter since orderby isn't implemented: https://openui5.hana.ondemand.com/#/api/sap.tnt.NavigationListItem

 <tnt:NavigationList id="navigationList">
    <tnt:NavigationListItem icon="sap-icon://"
                            expanded="{expanded}"
                            key="{MY_KEY}"
                            items="{/MY_PATH}">
       <tnt:NavigationListItem text="{MY_CONTENT}" key="{MY_KEY}"></tnt:NavigationListItem>
    </tnt:NavigationListItem>
  </tnt:NavigationList>

Here is an attempt to add a sorter that presently doesn't work. First, because return items correspond to the first level of NavigationListItem and secondly probably because of a wrong approach.

        var oNavigationList = this.getView().byId('navigationList');
        var aSorter = [];
        aSorter[0] = new sap.ui.model.Sorter('MY_FIELD', true);
        var oItems = oNavigationList.getItems();
        oItems.sort("items", {path: "/MY_PATH", sorter: aSorter});

Could you please advice on the appropriate approach?

Body:
Since your List is bound to ODATA Model, all the sorting will happen at server side. Every time you say, sort, a new request is sent to server. Now, as you said, the server is not supporting orderby, I suggest the following approach :

Do oDataModel.read and store the data in a JSON Model. oData Read API

Apply the sorting in XML using below code:

    <tnt:NavigationList id="navigationList">
        <tnt:NavigationListItem icon="sap-icon://"
            expanded="{expanded}" key="{MY_KEY}"
            items="{
                        path : '/MY_PATH',
                        sorter: {
                            path: 'MY_CONTENT',
                            descending: false,
                            group: false
                        },
                    }">
            <tnt:NavigationListItem text="{MY_CONTENT}"
                key="{MY_KEY}"></tnt:NavigationListItem>
        </tnt:NavigationListItem>
    </tnt:NavigationList>

Let me know if you need more information.


问:
Convert for loop to recursive function in python3
Is it possible to convert the following loop:

```<code>c = 0
for a in find:
    c += 1
return c
</code>```

Where find is a list like [a,b,c,d] to a function that uses recursion without using external libraries?

答:
I managed to do so by defining the following function:

```<code>def ric(find, n, c):
    if n < len(find):
        if find[n] in find:
            c += 1
        n += 1
        return ric(find, n, c)
    else:
        return c
</code>```

and calling it with ric(find, 0, 0)

Body:
Is it possible to convert the following loop:

c = 0
for a in find:
    c += 1
return c

Where find is a list like [a,b,c,d] to a function that uses recursion without using external libraries?

Body:
I managed to do so by defining the following function:

def ric(find, n, c):
    if n < len(find):
        if find[n] in find:
            c += 1
        n += 1
        return ric(find, n, c)
    else:
        return c

and calling it with ric(find, 0, 0)


问:
Use googletest with a function that gets input from user via std::cin?
How can I use googletest to test a function that relies on user input via std::cin?

For the example below, I'm looking for whatever code would allow me to add "2\n" to the std::cin stream so that the readUserInput() function reads 2 into the value variable and doesn't require any input from the user.

```<code>#include <iostream>
#include "gtest/gtest.h"

int readUserInput()
{
    int value;
    std::cout << "Enter a number: ";
    std::cin >> value;

    return value;
}

TEST(cin_test, Basic)
{
    // need code here to define "2\n"
    // as the next input for std::cin

    ASSERT_EQ(readUserInput(), 2);
}

int main(int argc, char **argv) {
    ::testing::InitGoogleTest(&argc, argv);
    return RUN_ALL_TESTS();
}
</code>```

答:
Add an argument to your function:</p>

```<code>int readUserInput(std::istream& input)
{
    int value;
    std::cout << "Enter a number: ";
    input >> value;
    return value;
}

TEST(Some, Test) {
    std::ifstream ifs;
    ifs.open("someFile", std::ifstream::in);
    // in production code pass std::cin
    std::cout << "readUserInput from std::cin: " << readUserInput(std::cin) << std::endl;
    // in testing pass some mock data from the file (or whatever)
    std::cout << "readUserInput from ifs: " << readUserInput(ifs) << std::endl;
    ifs.close();
}
</code>```

Body:
How can I use googletest to test a function that relies on user input via std::cin?

For the example below, I'm looking for whatever code would allow me to add "2\n" to the std::cin stream so that the readUserInput() function reads 2 into the value variable and doesn't require any input from the user.

#include <iostream>
#include "gtest/gtest.h"

int readUserInput()
{
    int value;
    std::cout << "Enter a number: ";
    std::cin >> value;

    return value;
}

TEST(cin_test, Basic)
{
    // need code here to define "2\n"
    // as the next input for std::cin

    ASSERT_EQ(readUserInput(), 2);
}

int main(int argc, char **argv) {
    ::testing::InitGoogleTest(&argc, argv);
    return RUN_ALL_TESTS();
}

Body:
Add an argument to your function:

int readUserInput(std::istream& input)
{
    int value;
    std::cout << "Enter a number: ";
    input >> value;
    return value;
}

TEST(Some, Test) {
    std::ifstream ifs;
    ifs.open("someFile", std::ifstream::in);
    // in production code pass std::cin
    std::cout << "readUserInput from std::cin: " << readUserInput(std::cin) << std::endl;
    // in testing pass some mock data from the file (or whatever)
    std::cout << "readUserInput from ifs: " << readUserInput(ifs) << std::endl;
    ifs.close();
}


问:
$Watch not working while watch text scope variable from the controller
$Watch not working while watch text scope variable from the controller. But scope is available for function call like "ng-blur", why not for $watch. What is wrong with my flow. Can anyone give suggestion

Html

```<code><div ng-include="'html/bar.html'" ng-controller="barController"></div>
<div class="content-container view" data-ui-view>
    </div>
</code>```

bar.html

```<code><li data-ng-repeat="menu in menuData.menus" data-ng-init="menu.showSubMenu = false">
        <a class="main-menu" data-ng-click="menu.showSubMenu = !menu.showSubMenu">{{menu.menuName}}</a>
          <div data-ng-show="menu.showSubMenu && menu.subMenus" class="top-nav-menu-wrapper" style="display: block;">
              <div class="menu-container">
                <div class="drop-down-menu" style="display: block; top:10px;">
                  <div class="container-left" data-equalizer="">
                    <ul>
Menus li
</ul></div></div></div></div></li>
</code>```

barController

```<code>$scope.$watch('menu.showSubMenu', function(newValue, oldValue) {
 console.log(newValue);
 if (newValue !== oldValue) {
  // stuff
 }
});

also tried this
$scope.$watchCollection('menu', function(newValue, oldValue) {
 console.log(newValue);
 if (newValue !== oldValue) {
  // stuff
 }
});
</code>```

I gave context html only for understand scenario. here I need to access "menu.showSubMenu" variable from controller also update from controller while click on window to close sub menu

答:
I tried this and working fine</p>

```<code>(function () {
"use strict";
define([], function () {
    return ['constantService', '$timeout', '$window', '$rootScope', function (constantService, $timeout, $window, $rootScope) {
        return {
                restrict: 'EA',
                scope: false,
                link: function(scope, element, attr) {
                  var w = angular.element($window);
                  var tempScope = '';
                  console.log(scope.$parent.menu);
                  scope.$parent.$watch('menu.showSubMenu', function(newValue, oldValue, scope) {
                    console.log(newValue);
                    if (newValue !== oldValue) {
                      tempScope = scope;
                    }
                  });
                  w.bind('click', function(event){
                    var currentEle = $(event.target);
                    //$rootScope.$broadcast('dropdown:close');
                    if(tempScope.menu && !(currentEle.hasClass('main-menu'))) {
                      console.log(tempScope);
                      tempScope.menu.showSubMenu=false;
                      tempScope.$apply();
                    }
                  });
                }
            };
    }];
});
}());
</code>```

Body:
$Watch not working while watch text scope variable from the controller. But scope is available for function call like "ng-blur", why not for $watch. What is wrong with my flow. Can anyone give suggestion

Html

bar.html

        {{menu.menuName}}

Menus li

barController

$scope.$watch('menu.showSubMenu', function(newValue, oldValue) {
 console.log(newValue);
 if (newValue !== oldValue) {
  // stuff
 }
});

also tried this
$scope.$watchCollection('menu', function(newValue, oldValue) {
 console.log(newValue);
 if (newValue !== oldValue) {
  // stuff
 }
});

I gave context html only for understand scenario. here I need to access "menu.showSubMenu" variable from controller also update from controller while click on window to close sub menu

Body:
I tried this and working fine

(function () {
"use strict";
define([], function () {
    return ['constantService', '$timeout', '$window', '$rootScope', function (constantService, $timeout, $window, $rootScope) {
        return {
                restrict: 'EA',
                scope: false,
                link: function(scope, element, attr) {
                  var w = angular.element($window);
                  var tempScope = '';
                  console.log(scope.$parent.menu);
                  scope.$parent.$watch('menu.showSubMenu', function(newValue, oldValue, scope) {
                    console.log(newValue);
                    if (newValue !== oldValue) {
                      tempScope = scope;
                    }
                  });
                  w.bind('click', function(event){
                    var currentEle = $(event.target);
                    //$rootScope.$broadcast('dropdown:close');
                    if(tempScope.menu && !(currentEle.hasClass('main-menu'))) {
                      console.log(tempScope);
                      tempScope.menu.showSubMenu=false;
                      tempScope.$apply();
                    }
                  });
                }
            };
    }];
});
}());


问:
Areapath is not correct in programmatically generated TFS/VSTS workitem
I am trying to create workitem in TFS/VSTS with custom area-path

for example I want to be my  workitem areapath to be "cde"

Area-paths in my project are similar to below

My patch element for area-path is as below.

```<code>{
    "op": "add",
    "path": "/fields/System.AreaPath",
    "value": "VSTS_TFS_Test\abc\cde"
  }
</code>```

I am using batch create method for this hence REST call url is as below

```<code><<my tfs collection url>>/_apis/wit/$batch?api-version=1.0
</code>```

However this always just display project file (VSTS_TFS_Test) as area in work item. Please refer below screen.

I wanted to "cde" to be area in work item. Please provide some guidence to make this possible

references :
https://www.visualstudio.com/en-us/docs/integrate/api/wit/work-items

答:
The problem should be your patch element.

Just try below patch element:

```<code>{
    "op": "add",
    "path": "/fields/System.AreaPath",
    "value": "VSTS_TFS_Test\\abc\\cde"
}
</code>```

After the patch, just refresh your browser to check the work item again.

Body:
I am trying to create workitem in TFS/VSTS with custom area-path

for example I want to be my  workitem areapath to be "cde"

Area-paths in my project are similar to below

My patch element for area-path is as below.

{
    "op": "add",
    "path": "/fields/System.AreaPath",
    "value": "VSTS_TFS_Test\abc\cde"
  }

I am using batch create method for this hence REST call url is as below

<<my tfs collection url>>/_apis/wit/$batch?api-version=1.0

However this always just display project file (VSTS_TFS_Test) as area in work item. Please refer below screen.

I wanted to "cde" to be area in work item. Please provide some guidence to make this possible

references :
https://www.visualstudio.com/en-us/docs/integrate/api/wit/work-items

Body:
The problem should be your patch element.

Just try below patch element:

{
    "op": "add",
    "path": "/fields/System.AreaPath",
    "value": "VSTS_TFS_Test\\abc\\cde"
}

After the patch, just refresh your browser to check the work item again.


问:
Script works on AWS EC2, but not on AWS Lambda after zipping
I am creating a simple AWS Lambda function using M2Crypto library. I followed the steps for creating deployment package from here. The lambda function works perfectly on an EC2 Linux instance (AMI).

This is my Function definition:

CloudOAuth.py

from M2Crypto import BIO, RSA, EVP
def verify(event, context):
  pem = "-----BEGIN PUBLIC KEY-----\n{0}\n-----END PUBLIC KEY-----".format("hello")
  bio = BIO.MemoryBuffer(str.encode(pem))
  print(bio)
  return
```

<h1>Deployment Package structure:</h1>

<p><img src="https://i.stack.imgur.com/SPoUO.png" alt="Lambda Deployment Package skeleton"></p>

<p>When I run the Lambda, I get the following issue and I also tried including libcrypto.so.10 from /lib64 directory, but didn't help. </p>

<h1>Issue when running Lambda</h1>

<blockquote>
  <p>/var/task/M2Crypto/_m2crypto.so: symbol sk_deep_copy, version libcrypto.so.10 not defined in file libcrypto.so.10 with link time reference`</p>
</blockquote>

```Python: 2.7
M2Crypto: 0.27.0
```

答: I would guess that the M2Crypto was built with different version of OpenSSL than what's on Lambda. See the relevant code. If not (the upstream maintainer speaking here), please, file a bug at https://gitlab.com/m2crypto/m2crypto/issues</p>

Body:
I am creating a simple AWS Lambda function using M2Crypto library. I followed the steps for creating deployment package from here. The lambda function works perfectly on an EC2 Linux instance (AMI).

This is my Function definition:

CloudOAuth.py

from M2Crypto import BIO, RSA, EVP
def verify(event, context):
  pem = "-----BEGIN PUBLIC KEY-----\n{0}\n-----END PUBLIC KEY-----".format("hello")
  bio = BIO.MemoryBuffer(str.encode(pem))
  print(bio)
  return

Deployment Package structure:

When I run the Lambda, I get the following issue and I also tried including libcrypto.so.10 from /lib64 directory, but didn't help.

Issue when running Lambda

/var/task/M2Crypto/_m2crypto.so: symbol sk_deep_copy, version libcrypto.so.10 not defined in file libcrypto.so.10 with link time reference`

Python: 2.7
M2Crypto: 0.27.0


问:
Not able to install @angular/cli using npm
I am new to "@angular/cli", I have tried every thing but could not install.
I am tired now please some one can help me to install angular 4 "@angular/cli" in my system.

node and npm Version

and when I am installing "npm install -g @angular/cli" then getting

after installing node -v 6 I am getting below erorr

please suggest me where actually I am wrong.

答:
Perhaps you are missing space- npm install -g @angular/cli@latest

```<code> 1) Install node.js and check and node -v in command prompt. (Node.js
       through website).
2) npm install -g @angular/cli@latest (run in command prompt).
Refer this link for further help
</code>```

https://github.com/angular/angular-cli

```<code>    3) Check node -version
    4) Check npm -version
    5) check ng -version

    If already installed, then uninstall it and install again,
    npm uninstall -g @angular/cli
    npm cache clean
</code>```

Body:
Perhaps you are missing space- npm install -g @angular/cli@latest

 1) Install node.js and check and node -v in command prompt. (Node.js
       through website).
2) npm install -g @angular/cli@latest (run in command prompt).
Refer this link for further help

https://github.com/angular/angular-cli

    3) Check node -version
    4) Check npm -version
    5) check ng -version

    If already installed, then uninstall it and install again,
    npm uninstall -g @angular/cli
    npm cache clean