#StackBounty: #views #8 #database Displaying data by views from external API

Bounty: 100

I have made 2 db’s one is of Drupal 8 and another is external, that is non-SQL(CouchDB) and to manage this DB I made an API with different endpoint(URLs) witch I can axes them by GET/POST methods. After correct request I get response
in JSON from my API.

When I create for example a user, content(node) or something else I save some info in my Drupal database and some info in my API db (I made alteration of the forms that create content by hook_form_BASE_FORM_ID_alter and this way I create content in my external DB).

My question is how to Display a data from a views, combining information form two Data-Bases?


Get this bounty!!!

#StackBounty: #magento2 #database #magento-enterprise #catalog #magento2-enterprise Magento 2 EE – fetch products with sales data on sp…

Bounty: 100

I have a M2 custom extension which fetches products with its sales data from database.

Current code:

/** @var $products MagentoCatalogModelResourceModelProductCollection $productCollection */
$products = $this->objectManager->create('MagentoCatalogModelResourceModelProductCollection');

// ... adding some filters to $collection

// Enrich collection with sales data
/** @var MagentoFrameworkAppResourceConnection $resource */
$resource = $objectManager->create('MagentoFrameworkAppResourceConnection');
$ordersTableName = $resource->getTableName('sales_order_item');
$superTableName = $resource->getTableName('catalog_product_super_link');

if ($this->productHelper->isAttributeEnabled($additionalAttributes, 'ordered_qty')) {
    $collection->getSelect()->columns('(SELECT SUM(qty_ordered) FROM ' . $ordersTableName . ' AS o LEFT JOIN ' . $superTableName . ' AS l ON l.product_id = o.product_id WHERE o.product_id = e.entity_id OR l.parent_id = e.entity_id) as ordered_qty');
}

// Process products
/** @var Product $product */
foreach ($collection as $product) {
    // do something
}

It works pretty well on both CE and EE. However when I turn on “split database” feature of M2 EE it breaks as sales data are located in a different database than products.

Error:

SQLSTATE[42S02]: Base table or view not found: 1146 Table 'magento21ee.sales_order_item' doesn't exist, query was: ...

The questions is – what is the best practice to handle that? Should I remove the sales data from the collection and perform a separate query to fetch sales data for each product? It seems to be really not effective.

Should I fetch all products, all sales data and then pair it which processing? It’ll add additional complexity to the code, but will be much more effective than querying DB for each product.

Or is there any hidden Magento feature how to fetch products with sales data?

Thank a bunch for any help!


Get this bounty!!!

#StackBounty: #c# #sql-server #database #entity-framework #code-first why code first want to create index column?

Bounty: 50

I have two table like below:

[Table("MyFlashCard")]
public partial class MyFlashCard
{
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
    public MyFlashCard()
    {
        MyFlashCardPics = new HashSet<MyFlashCardPic>();
    }
    public int Id { get; set; }

    public int? FaslID { get; set; }

    public virtual FasleManJdl FasleManJdl { get; set; }

    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
    public virtual ICollection<MyFlashCardPic> MyFlashCardPics { get; set; }
}

[Table("MyFlashCardPic")]
public partial class MyFlashCardPic
{
    public int Id { get; set; }

    [ForeignKey("MyFlashCard")]
    public int MyFlashCardId { get; set; }

    public virtual MyFlashCard MyFlashCard { get; set; }
}

and

  protected override void OnModelCreating(DbModelBuilder modelBuilder)
  {
        modelBuilder.Entity<MyFlashCard>()
            .HasMany(e => e.MyFlashCardPics)
            .WithRequired(e => e.MyFlashCard)
            .HasForeignKey(e => e.MyFlashCardId)
            .WillCascadeOnDelete();
  }

and when i add migration it will create the below code

CreateTable(
            "dbo.MyFlashCardPic",
            c => new
                {
                    Id = c.Int(nullable: false, identity: true),
                    MyFlashCardId = c.Int(nullable: false),
                    MyFlashCard_Id = c.Int(),
                    MyFlashCard_Id1 = c.Int(),
                })
            .PrimaryKey(t => t.Id)
            .ForeignKey("dbo.MyFlashCard", t => t.MyFlashCard_Id)
            .ForeignKey("dbo.MyFlashCard", t => t.MyFlashCard_Id1)
            .ForeignKey("dbo.MyFlashCard", t => t.MyFlashCardId, cascadeDelete: true)
            .Index(t => t.MyFlashCardId)
            .Index(t => t.MyFlashCard_Id)
            .Index(t => t.MyFlashCard_Id1);

i have just MyFlashCardId column in MyFlashCardPic table but it want to create another column like: MyFlashCard_Id,MyFlashCard_Id1

I want to know why this happens and how prevent it?

when i delete these columns from above migration,after creating database(with update-database command) it will throws below error when i want to use MyFlashCardPic entity

Invalid column name ‘MyFlashCard_Id1’ , Invalid column name ‘MyFlashCard_Id’

and when i don’t delete these columns from migration i have problem in editing flashcard that have pics like another question i have recently

How to find out context objects that one entity is attached to?

another point is that without

[ForeignKey("MyFlashCard")]

attribute it will create 3 index column and without

modelBuilder.Entity<MyFlashCard>()
            .HasMany(e => e.MyFlashCardPics)
            .WithRequired(e => e.MyFlashCard)
            .HasForeignKey(e => e.MyFlashCardId)
            .WillCascadeOnDelete();

in OnModeling it will create 4 index column


Get this bounty!!!

#StackBounty: #database #drush #mamp Drush 8 database connection issue

Bounty: 50

I’m having trouble connecting to my databases. It worked a few days ago but can’t seem to figure why it doesn’t work now. It’s a D8-site. Drush version is 8.1.12.

I’ve double-checked both the alias-file and settings.php-files (both on server & local) and database settings all appear correct. My local site runs on Mamp 4.1.1. and network access is allowed.

Here’s the error I get when running sql-sync drush sql-sync @local @dev. Rsync works fine.

Unable to load class DrushSqlSql                                                                                                                                                                                                                                 [error]
exception 'DrushSqlSqlException' with message 'Unable to find a matching SQL Class. Drush cannot find your database connection details.' in /vendor/drush/drush/commands/sql/sql.drush.inc:541                [error]
Stack trace:
#0 /vendor/drush/drush/commands/sql/sql.drush.inc(252): drush_sql_get_class()
#1 [internal function]: drush_sql_dump()
#2 /vendor/drush/drush/includes/command.inc(422): call_user_func_array('drush_sql_dump', Array)
#3 /vendor/drush/drush/includes/command.inc(231): _drush_invoke_hooks(Array, Array)
#4 [internal function]: drush_command()
#5 /vendor/drush/drush/includes/command.inc(199): call_user_func_array('drush_command', Array)
#6 /vendor/drush/drush/lib/Drush/Boot/BaseBoot.php(67): drush_dispatch(Array)
#7 /vendor/drush/drush/includes/preflight.inc(66): DrushBootBaseBoot->bootstrap_and_dispatch()
#8 /vendor/drush/drush/drush.php(12): drush_main()
#9 {main}
sql-dump failed.

And here’s the aliases.drushrc.php (my local copy of course includes credentials:))

<?php

/* Tambourhinoceros Site */

$aliases["local"] = array (
 'root' => '', // local path
 'uri' => 'local.tambourhinoceros.net',  // local uri
 'path-aliases' =>
 array (
   '%dump-dir' => '/tmp',
   '%drush' => '/usr/local/bin',
   '%site' => 'sites/default',
 ),
 'remote-user' => 'tamb_dev', 
 'ssh-options' => '-T',
 'databases' =>
 array (
   'default' =>
   array (
     'default' =>
     array (
        'database' => '', // local database 
        'username' => '', // local database user
        'password' => '', // local database pw
        'host' => 'localhost',
        'port' => '8889',
        'namespace' => 'Drupal\Core\Database\Driver\mysql',
        'driver' => 'mysql',
        'prefix' => '',
      ),
    ),
  ),
);

$aliases["dev"] = array (
  'root' => '', // remote path
  'uri' => 'drupal-85630-297475.cloudwaysapps.com', // remote uri
  'path-aliases' =>
  array (
  ),
  'remote-host' => '', remote IP
  'remote-user' => '', remote User
  'command-specific' =>
 array (
   'rsync' =>
   array (
     'mode' => 'rlptzO',
     'verbose' => true,
     'no-perms' => true,
   ),
 ),
 'databases' =>
 array (
   'default' =>
   array (
     'default' =>
     array (
        'database' => '', remote database
        'username' => '', remote  user
        'password' => '', remote pw
        'host' => 'localhost',
        'port' => '',
        'driver' => 'mysql',
        'prefix' => '',
      ),
    ),
  ),
)
?>


Get this bounty!!!

#StackBounty: #tomcat #java #database #cpu-usage #memory-usage Tomcat consuming more Memory after Application Data is added .

Bounty: 50

A Tomcat Server suddenly shows an increment of 2 GB memory consumption after adding more data into the Application or Application Oracle Database. What I mean is that after restart Tomcat is normal but after a few hours it shows approx. 2000MB of Memory space . There are 4 Servers and the numbers are close in all the four . Before the data adjoined it was behaving appropriate . Please some one could specify whether this more data in Database which is causing the problem or something else needs to be looked into ??

Parallelly the CPU wait cycles have increased during this time frame .


Get this bounty!!!

#StackBounty: #magento-2.1 #product #database #attributes Where does Magento store the PRODUCT_HAS_WEIGHT attribute

Bounty: 50

For some of our configured products (unfortunately), the PRODUCT_HAS_WEIGHT attribute has not been set to 1. Although, the products all have weight set (as a float), Magento still refuses to provide shipping calculation for these products.

I want to modify the PRODUCT_HAS_WEIGHT property for all products in the database. For that I want to access the MySQL database and issue a query that updates all the corresponding fields. Unfortunately, I was unsuccessful in identifying the attribute to update.

For example, to examine the weights, I would do the following:

 (1) USE database;
 (2) SELECT * FROM eav_attribute where attribute_code LIKE '%weight%';
 (3) SELECT * FROM catalog_product_entity_decimal where attribute_id = 82;`

In a similar fashion as in line (2), product has weight is not found.

I’d kindly ask the community, to help me find the related attribute to update for each product.

I’m using Magento CE 2.1.6.


Get this bounty!!!

#StackBounty: #php #mysql #database #rest #codeigniter Create API endpoint for fetching dynamic data based on time

Bounty: 50

I have a scraper which periodically scrapes articles from news sites and stores them in a database [MYSQL].
The way the scraping works is that the oldest articles are scraped first and then i move onto much more recent articles.

For example an article that was written on the 1st of Jan would be scraped first and given an ID 1 and an article that was scraped on the 2nd of Jan would have an ID 2.

So the recent articles would have a higher id as compared to older articles.

There are multiple scrapers running at the same time.

Now i need an endpoint which i can query based on timestamp of the articles and i also have a limit of 10 articles on each fetch.

The problem arises for example when there are 20 articles which were posted with a timestamp of 1499241705 and when i query the endpoint with a timestamp of 1499241705 a check is made to give me all articles that is >=1499241705 in which case i would always get the same 10 articles each time,changing the condition to a > would mean i skip out on the articles from 11-20. Adding another where clause to check on id is unsuccessful because articles may not always be inserted in the correct date order as the scraper is running concurrently.

Is there a way i can query this end point so i can always get consistent data from it with the latest articles coming first and then the older articles.

EDIT:

   +-----------------------+
   |   id | unix_timestamp |
   +-----------------------+
   |    1 |   1000         |
   |    2 |   1001         |
   |    3 |   1002         |
   |    4 |   1003         |
   |   11 |   1000         |
   |   12 |   1001         |
   |   13 |   1002         |
   |   14 |   1003         |
   +-----------------------+

The last timestamp and ID is being sent through the WHERE clause.

E.g.
$this->db->where('unix_timestamp <=', $timestamp);
$this->db->where('id <', $offset);
$this->db->order_by('unix_timestamp ', 'DESC');
$this->db->order_by('id', 'DESC');

On querying with a timestamp of 1003, ids 14 and 4 are fetched. But then during the next call, id 4 would be the offset thereby not fetching id 13 and only fetching id 3 the next time around.So data would be missing .


Get this bounty!!!

#StackBounty: #database #images #files #managed-files Is it possible to convert separate (matching) managed files to a single shared fi…

Bounty: 50

I’m finishing up a Commerce store that has tens of thousands of imported products. In a slightly simplified form, here’s my problem: Each has a unique primary image and a common secondary image. The import has created a separate secondary image of the exact same file for each product, which obviously creates enormous redundancy and uses much disk space unnecessarily.

Is there a safe way to convert all of the secondary images into a single, shared image in the database and filesystem? (I’m envisioning some kind of database SQL to make multiple products reference the same image, and then hoping that Drupal will clean up the orphan files automatically — am I looking in the right direction?)

(Drupal 7)


Get this bounty!!!

#StackBounty: #magento2 #database #attributes #event-observer #media-images how to save image custom attribute in magento 2

Bounty: 200

preview in backend

preview in backend 2

I need to display few images of product in frontend based on condition: use for virtual mirror should be checked.

<?php
/**
 * Copyright © Magento, Inc. All rights reserved.
 * See COPYING.txt for license details.
 */

namespace DcwVmObserver;

use MagentoFrameworkEventObserverInterface;

class ChangeTemplateObserver extends MagentoProductVideoObserverChangeTemplateObserver
{
    /**
     * @param mixed $observer
     * @SuppressWarnings(PHPMD.UnusedFormalParameter)
     * @return void
     */
    public function execute(MagentoFrameworkEventObserver $observer)
    {
        $observer->getBlock()->setTemplate('Dcw_Vm::helper/gallery.phtml');
    }
}

Template:

" value="1" class="admin__control-checkbox" name="[][vm]" checked="checked" />
</div> </div>

Install script:

<?php

namespace DcwVmSetup;

use MagentoFrameworkSetupInstallSchemaInterface;
use MagentoFrameworkSetupSchemaSetupInterface;
use MagentoFrameworkSetupModuleContextInterface;
use MagentoCatalogModelResourceModelProductGallery;

class InstallSchema implements InstallSchemaInterface {

    public function install(SchemaSetupInterface $setup, ModuleContextInterface $context) {
        $setup->startSetup();

        $setup->getConnection()->addColumn(
                $setup->getTable(Gallery::GALLERY_TABLE), 'vm', [
            'type' => MagentoFrameworkDBDdlTable::TYPE_SMALLINT,
            'unsigned' => true,
            'nullable' => false,
            'default' => 0,
            'comment' => 'use for Vm'                ]
        );

        $setup->endSetup();
    }

}

How to save checked images state in backend? And how to filter those images in frontend? Can you help me on this?

UPDATE:

following observer (on event catalog_product_save_after) for existing images is working, but for new images not working.

<?php

namespace DcwVmObserver;

use MagentoFrameworkEventObserverInterface;

class Productsaveafter implements ObserverInterface {

    protected $request;
    protected $resource;

    /**
     * 
     * @param MagentoFrameworkAppRequestInterface $request
     * @param MagentoFrameworkAppResourceConnection $resource
     */
    public function __construct(
    MagentoFrameworkAppRequestInterface $request, MagentoFrameworkAppResourceConnection $resource
    ) {
        $this->request = $request;
        $this->resource = $resource;
    }

    public function execute(MagentoFrameworkEventObserver $observer) {

        $vm = array();
        $data = $this->request->getPostValue();

        if (isset($data['product']['media_gallery']['images'])) {
            $images = $data['product']['media_gallery']['images'];

            foreach ($images as $image) {
                if (isset($image['vm']) && $image['vm'] == 1) {
                    $vm[$image['value_id']] = 1;
                } else {
                    $vm[$image['value_id']] = 0;
                }
            }
   // print_r($images);exit;
            $connection = $this->resource->getConnection();
            $tableName = 'catalog_product_entity_media_gallery'; //gives table name with prefix
            $product = $observer->getProduct();
            $mediaGallery = $product->getMediaGallery();

            if (isset($mediaGallery['images'])) {
                foreach ($mediaGallery['images'] as $image) {
                    if (isset($vm[$image['value_id']])) {
                        //Update Data into table
                        $sql = "Update " . $tableName . " Set vm = " . $vm[$image['value_id']] . " where value_id = " . $image['value_id'];
                        $connection->query($sql);
                    }
                }
            }
        }
    }

}


Get this bounty!!!

#StackBounty: #swift #database How to efficiently check database object based on location/proximity to user's location?

Bounty: 50

I am constructing an app (in XCode) which, in a general sense, displays information to users. The information is stored as individual objects in a database (happens to be a Parse-server hosted by heroku). The user can elect to “see” information that has been created within a set distance from their current location. (The information, when saved to the DB, is saved along with its lat and long based on the location of the user when they initiated the save). I know I can filter the pieces of information by comparing their lat and long to the viewing user’s current lat and long and only display those which are close in enough. Roughly/generally:

var currentUserLat = latitude //latitude of user's current location
var infoSet = [Objects] //set of all pulled info from DB
for info in infoSet{
    if info.lat-currentUserLat < 3{//arbitrary value
       //display the info
    }else{
       //don't display
    }
}

This is set up decently enough, and it works fine. The reason it works fine, though, is because of the small number of entries in the DB at this current time (the app is in development). Under practical usage (ie many users) the DB may be full of information objects (lets say, a thousand). In my opinion, to individually pull and compare the latitude of the information and compare it to the current user’s latitude for each and every DB entry would take too long. I know there must be a way to do it in a timely manner (think tinder… they only display profiles of people who are in the near vicinity and it doesn’t take that long for them to do so despite millions of profiles) but I do not know what is most efficient. I thought of creating separate sections for different geographical regions in the DB and then only searching those particular section of the DB depending on where the user’s current location is, but this seems unsophisticated and would still lead to large amounts of info being pulled. What is the best way to do this?


Get this bounty!!!